This documentation relates to an earlier version of Confluence.
View this page in the current documentation or visit the current documentation home.
Skip to end of metadata
Comment: Corrected links that should have been relative instead of absolute.
Go to start of metadata

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

Update Statistics

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) );