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 (Creating a Lowercase Page Title Index).

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

Was this helpful?

Thanks for your feedback!

Why was this unhelpful?

Have a question about this article?

See questions about this article

Powered by Confluence and Scroll Viewport