This page last changed on Aug 20, 2009 by mryall.

Diagnosis

Confluence sometimes has performance problems retrieving pages by title because the query uses the lower() function. For example, the query looks something like this:

select * from CONTENT where lower(TITLE) = :title and SPACEID = :spaceid

Database profiling might show a query like the following taking a long time to execute (emphasis added):

select ... from CONTENT page0_, SPACES space1_
where page0_.CONTENTTYPE='PAGE'
and ((lower(space1_.SPACEKEY)= @P0 and page0_.SPACEID=space1_.SPACEID)
and(lower(page0_.TITLE)= @P1 )
and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))

Typically, databases don't use indexes when you use a function in a where clause; they do a table scan instead. This makes the performance of this query not ideal (CONF-11577).

Generic solution

On many databases (e.g. Oracle, PostgreSQL, DB2 for z/OS), it is possible to create the index using the normal "create index" syntax, just using the function instead of the column name.

create index CONFTITLE_LOWER on CONTENT(lower(TITLE));

Sources:

SQL Server

On SQL Server, you can add a computed column to the database table and then add an index on this column.

alter table CONTENT add TITLE_LOWER as lower(TITLE);
create index CONFTITLE_LOWER on CONTENT(TITLE_LOWER);

Sources:

MySQL

It is not currently possible to create a lowercase index on MySQL. Confluence 3.0 includes some caching improvements which should alleviate this performance problem on this database.

Source:

Workaround for MySQL databases, using a case-insensitive collation:

Please check whether your MySQL database has been set to use case-sensitive or case-insensitive collation. The queries to check whether your database is set to case-insensitive collation are:

show full columns from content where field = 'title';
show full columns from spaces where field = 'spacekey';

If the collation_name is returned as <encoding>_ci, the ci indicates case-insensitive collation.

If the database has been set to use case-insensitive collation, you can try removing lower from the following queries, in your ContentEntityObject.hbm.xml file residing in your <Confluence-Install>/confluence/WEB-INF/lib/confluence-2.x.x.jar/com/atlassian/confluence/core/:

<query name="confluence.page_findLatestBySpaceKeyTitle"><![CDATA[
	from Page page
	where lower(page.space.key) = :spaceKey and
		lower(page.title) = :pageTitle and
		page.originalVersion is null and
		page.contentStatus = 'current'
]]></query>

<query name="confluence.page_findLatestBySpaceKeyTitleOptimisedForComments"><![CDATA[
	from Page page 
	left join fetch page.comments as theComments
	left join fetch theComments.children 
	where lower(page.space.key) = :spaceKey and
		lower(page.title) = :pageTitle and
		page.originalVersion is null and
		page.contentStatus = 'current'
]]></query>

DB2 for Linux, Unix or Windows

DB2 supports indexes on generated columns which are used for queries with a matching predicate. You can implement it like this:

ALTER TABLE CONTENT ADD COLUMN TITLE_LOWER GENERATED ALWAYS AS (LOWER(TITLE));
CREATE INDEX CONFTITLE_LOWER ON CONTENT(TITLE_LOWER)

Related pages

Document generated by Confluence on Jul 09, 2010 01:09