Confluence Docs 3.3 : Creating a Lowercase Page Title Index
This page last changed on Aug 20, 2009 by mryall.
DiagnosisConfluence 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):
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 solutionOn 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 ServerOn 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:
MySQLIt 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:
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, Unix or WindowsDB2 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 |
![]() |
Document generated by Confluence on Jul 09, 2010 01:09 |