This page last changed on Jan 05, 2007 by david.soul@atlassian.com.

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

Any tips on MySQL 3.x or 4.x optimizations?

Posted by jason.dillon@paybytouch.com at Sep 16, 2005 18:19

As written above, the indexing should work with all other DB's including MySQL.

Posted by jens@atlassian.com at Sep 19, 2005 00:30

As far as I can tell the indexes listed above similar to:

CREATE INDEX CONTENT_IDX7 ON CONTENT (PREVVER)
PARALLEL ( DEGREE 4 INSTANCES 1 );

Will not work on MySQL, hence the question was for MySQL specific optimizations.

Posted by jason.dillon@paybytouch.com at Sep 19, 2005 02:04

For details on how to create an index on mysql have a look at http://dev.mysql.com/doc/mysql/en/create-index.html.

The basic syntax is the same as the one you can find above. Only the _PARALLEL ( DEGREE 2 INSTANCES 1 );_ part is database specific.

Posted by jens@atlassian.com at Sep 20, 2005 21:21
Recent version of confluence already implement these suggestions.
Recent versions of Confluence (tested on 2.3.3) appear to already implement these indexes.  On MySQL4.1 the database is created with these indexes by default.  Your database may still benefit from different optimizations, depending on your usage.
Posted by noster at Mar 03, 2007 20:35
Document generated by Confluence on Mar 22, 2007 20:58