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