Confluence Docs 3.0 : Creating a Lowercase Page Title Index This page last changed on Aug 20, 2009 by mryall. ### 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: http://www.postgresql.org/docs/current/static/sql-createindex.html http://asktom.oracle.com/tkyte/article1/ ### 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: http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).aspx http://blogs.msdn.com/psssql/archive/2009/03/09/how-to-use-computed-columns-to-improve-query-performance.aspx ### 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: http://dev.mysql.com/doc/refman/5.1/en/create-index.html ##### 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, Unix 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 Improving Database Performance CONF-10030: Queries that use 'lower' do not use index because of case sensitivity

Document generated by Confluence on Nov 05, 2009 23:34