Creating a Lowercase Page Title Index

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), 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.

Source:

http://dev.mysql.com/doc/refman/5.1/en/create-index.html

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

Please check whether your MySQL DB has been set to use case-sensitive or case-insensitive collation.
The queries to check whether your DB is set to be case-insensitive 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 DB 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>

Page tree