Summary

The index c_si_ct_pv_cs_cd_idx on CONTENT table is recreated with wrong definition when confluence is upgraded under MySQL.
This also affects MS SQL Server and PostgreSQL.

Environment

• MySQL 5.6.32, MS SQL Server, and PostgreSQL

Steps to Reproduce

1. Install any older Confluence in a linux instance. e.g. Confluence 5.10.8
2. Upgrade the Confluence to latest version e.g. Confluence 6.10.0
3. Then issue the following command

   show create table CONTENT;
   

Expected Results

The definition of the c_si_ct_pv_cs_cd_idx shall be as below.

  KEY `c_si_ct_pv_cs_cd_idx` (`SPACEID`,`CONTENTTYPE`,`PREVVER`,`CONTENT_STATUS`,`CREATIONDATE`),

Note: Expected as above because this is the definition on the clean install.

Actual Results

The definition of the c_si_ct_pv_cs_cd_idx is as below.

  KEY `c_si_ct_pv_cs_cd_idx` (`CREATIONDATE`,`PREVVER`,`SPACEID`,`CONTENTTYPE`,`CONTENT_STATUS`),

Notes

This issue is critical because this can trigger a performance degradation such as CONFSERVER-52429 letting DB query optimizer wrongly choose this multi-column index, c_si_ct_pv_cs_cd_idx.
It is assumed that queries that have order by CREATIONDATE are looked up by the index specifies CREATIONDATE in a first column because of the description in https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html
It is assumed that the index lookup against "SPACEID" or "CONTENTTYPE" becomes too inefficient if these columns are set in 3rd or 4th place.

Workaround

For MySQL database
Manually recreate the index.

-- Drop the index
ALTER TABLE CONTENT DROP INDEX c_si_ct_pv_cs_cd_idx ;

-- Create the index manually
CREATE INDEX
    `c_si_ct_pv_cs_cd_idx`
ON
    `<Database Name>`.`CONTENT`
    (
        `SPACEID`,
        `CONTENTTYPE`,
        `PREVVER`,
        `CONTENT_STATUS`,
        `CREATIONDATE`
    )

For MS SQL SERVER

• Delete the current index definition.

  GO
  DROP INDEX c_si_ct_pv_cs_cd_idx   
      ON [dbo].[CONTENT];  
  GO
  

• Create the index using the expected column order.

  GO
  CREATE NONCLUSTERED INDEX [c_si_ct_pv_cs_cd_idx] ON [dbo].[CONTENT]
  (
  	[SPACEID] ASC,
  	[CONTENTTYPE] ASC,
  	[PREVVER] ASC,
  	[CONTENT_STATUS] ASC,
  	[CREATIONDATE] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  GO
  

For PostgreSQL

-- Drop the index
DROP INDEX c_si_ct_pv_cs_cd_idx;

-- Create the index manually
CREATE INDEX c_si_ct_pv_cs_cd_idx ON CONTENT (spaceid,
contenttype,
prevver,
content_status,
creationdate);