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:

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

  • No labels

Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.