This page last changed on Aug 17, 2008 by cmiller.
 | Use At Your Own Risk
We are in the process of revising the suggestions on this page. Some of the indexes (especially the BITMAP indexes) may harm performance more than they help, especially on busy Confluence servers. Please see the following JIRA issue for more information: CONF-12795. |
Configuring Database Indexing
 | This was done specificaly for Oracle 9i but the indexing should work with all other DB's if you remove 'bitmap' from the indexes which use it, and remove the parallel clauses.
The work request is followed here : http://jira.atlassian.com/browse/CONF-2780
Thanks to Michael Thorpe for this work |
Confluence performance can be improved by adding the following indexes :-
 | Create Indexes
create index CONTENT_IDX1 on CONTENT( SPACEID );
create index CONTENT_IDX2 on CONTENT( MAILSPACEID ); Confluence 1.4.x only
create index CONTENT_IDX3 on CONTENT( BLOGSPACEID ); Confluence 1.4.x only
create bitmap index CONTENT_IDX4 on CONTENT( CONTENTTYPE );
create index CONTENT_IDX5 on CONTENT( PARENTID );
create index CONTENT_IDX6 on CONTENT( TITLE );
create index INKS_IDX1 on LINKS( CONTENTID );
create index LINKS_IDX2 on LINKS( DESTSPACEKEY ); |
Also, ensure that all statistics are upto date for the Confluence schema ( Hibernate does not ensure that stats are gathered ) by using
DBMS_STATS.GATHER_SCHEMA_STATS
And yet more indexes ( These reduced physical IO by 80% on our database ) :-
 | Create Indexes
CREATE INDEX CONTENT_IDX7 ON CONTENT (PREVVER)
PARALLEL ( DEGREE 4 INSTANCES 1 );
CREATE BITMAP INDEX CONTENT_IDX9 ON CONTENT (CONTENT_STATUS);
CREATE INDEX CONTENT_IDX8 ON CONTENT (PAGEID)
PARALLEL ( DEGREE 4 INSTANCES 1 );
CREATE INDEX EXTRNLNKS_IDX1 ON EXTRNLNKS (CONTENTID)
PARALLEL ( DEGREE 4 INSTANCES 1 );
CREATE INDEX ATTACHMENTS_IDX1 ON ATTACHMENTS (PAGEID)
PARALLEL ( DEGREE 4 INSTANCES 1 );
CREATE INDEX SPACEPERMISSIONS_IDX1 ON SPACEPERMISSIONS (SPACEID)
PARALLEL ( DEGREE 2 INSTANCES 1 );
|
And one more
 | Create Indexes
CREATE INDEX CONTENT_IDX10 ON CONTENT (USERNAME) |
This one may also help (from Charles Miller)
 | Create Indexes
CREATE INDEX PROPERTY_IDX1 ON OS_PROPERTYENTRY (ENTITY_ID) |
Also, if your database supports function-based indexes (more about Oracle function based indexes here), this might help:
 | Create Indexes
create index CONTENT_IDX11 on CONTENT( lower(TITLE) ); |
|