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:
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.
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.
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:
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/:
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:
Add Comment