Creating a Lowercase Page Title Index
Confluence sometimes has performance problems retrieving pages by title because the query uses the lower() function. For example, the query looks something like this:
Database profiling might show a query like the following taking a long time to execute (emphasis added):
select ... from CONTENT page0_, SPACES space1_
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).
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.
On SQL Server, you can add a computed column to the database table and then add an index on this column.
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.
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
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:
Was this helpful?
Thanks for your feedback!