For some reason, Confluence indexes all DB indexes multiple times. This has several adverse effects:
1) The DB operates slower since it has to update multiple indexes instead of just one
2) The DB size requirements are significantly higher than with just one index
Example of one MySQL create statement from Confluence:
DROP TABLE IF EXISTS `SPACES`;
CREATE TABLE `SPACES` (
`SPACEID` bigint(20) NOT NULL default '0',
`SPACENAME` varchar(255) default NULL,
`SPACEKEY` varchar(255) NOT NULL default '',
`SPACEDESCID` bigint(20) default NULL,
`HOMEPAGE` bigint(20) default NULL,
`CREATOR` varchar(255) default NULL,
`CREATIONDATE` datetime default NULL,
`LASTMODIFIER` varchar(255) default NULL,
`LASTMODDATE` datetime default NULL,
PRIMARY KEY (`SPACEID`),
UNIQUE KEY `SPACEKEY` (`SPACEKEY`),
KEY `HOMEPAGE` (`HOMEPAGE`),
KEY `SPACEDESCID` (`SPACEDESCID`),
KEY `HOMEPAGE_2` (`HOMEPAGE`),
KEY `SPACEDESCID_2` (`SPACEDESCID`),
KEY `HOMEPAGE_3` (`HOMEPAGE`),
KEY `SPACEDESCID_3` (`SPACEDESCID`),
KEY `HOMEPAGE_4` (`HOMEPAGE`),
KEY `SPACEDESCID_4` (`SPACEDESCID`),
KEY `HOMEPAGE_5` (`HOMEPAGE`),
KEY `SPACEDESCID_5` (`SPACEDESCID`),
KEY `HOMEPAGE_6` (`HOMEPAGE`),
KEY `SPACEDESCID_6` (`SPACEDESCID`)
) TYPE=MyISAM;
where the HOMEPAGE and SPACEDESCID columns are indexed seven times for no reason.
The fix is to remove the duplicates from the indexes.
Partager