DB2 LUW does support indexes on column expressions through generated columns.
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.dbobj.doc/doc/c0020109.html

If this problem applies to DB2 it should also apply to PostgreSQL and Oracle. MySQL can be run with a case sensitive or insensitive collation.

I'm hoping we can just not do a case-insensitive search when looking up page titles. Any search that generic should probably using Lucene index rather than the Database.

There are other reports of this issue occurring with MySQL:
http://jira.atlassian.com/browse/CONF-9152 (check out comments)
https://support.atlassian.com/browse/CSP-11031

MySQL RFE: http://bugs.mysql.com/bug.php?id=9639

The title of this JIRA should be changed so that it's clear that this is a generic issue.

PostgresSQL can create indexes for functions, but since Confluence doesn't generate the index, users don't benefit from this unless a DBA creates the index manually.

Drupal was facing the very same issues in the past (http://drupal.org/node/83738), their attempt to resolving it is similar to what Patrick mentioned. I'm all for this approach as well.

I'll attach an updated version of the patch. We just experienced major outage today that lasted the entire afternoon. According to the db investigation this was due to slow queries that were using the lower() function. After the patch was deployed, the outage cleared.

I suggest that you consider this to be a blocker RFE/bug as it can take a site down easily.

There is one more major bug that we found in atlassian-user code, I'll file a separate issue for that.

cheers,
Igor

before: 10-15 seconds
after: 0.01-0.12 seconds

WHAT A DIFFERENCE!

My only concern is if the patch is cluster safe. We were running two nodes side by side, but since I don't understand all your clustering code (yet), I took one node down just to be safe. Can someone from Atlassian have a look at it and let us know if it's ok to run this in clustered mode?

cheers,
Igor

I'm afraid that you are wrong.

The patch is as database agnostic as possible and will work with any database out there. I avoided the use of triggers, stored procedures or any other database specific technologies that would by compatible with other relational database systems. I even went to the trouble of using some Atlassian's utility methods to make the patch compatible with DB2.

Any database that supports indexes (that is any serious db out there) will enormously benefit from this patch.

cheers,
Igor

As I've expressed earlier in this comments, I think the approach taken in your patch is probably the right one.

What Ivan was trying to express is that this patch is only required for some databases, whereas others will happily use their existing index despite the lower function. The patch is required for MySQL because it ignores indexes as soon as you introduce a function. The introduction of the column is definitely the most database-generic way of doing it.

I have looked quickly over your patch and cannot imagine a reason it would not work in a cluster. How did you migrate the data? By SQL? I am not sure when we will have time to test your patch, but I certainly hope we can do it soon.
However, in case we do not get to working your patch into the product soon, might I suggest a simpler patch for your particular instance?

MySQL will by default completely ignore case on data anyway. Certainly you could do it for this specific table, and in fact some of our customers run their whole mysql database with case insensitivity. We recommend against this because the majority of databases, and therefore of our testing, is done case sensitively. I suggest setting your content table's collation to be case insensitive, and simply taking out the call to the lower function in the queries that use it. cf http://dev.mysql.com/doc/refman/5.0/en/charset-table.html. That change will be smaller, and easier to maintain than the database agnostic one.

Of course, there are pitfalls to doing this, such as the chance of a unique key constraint collision where two keys differ only in case.

Cheers,
Don

So this problem is solvable in current releases of DB2 - but it requires user action to solve it.

This issue is a major problem for any site that has large tables. Basically, any customer that's going to have a large wiki installation - this issue is a show-stopper. The lower is used all over the place. Not just on content. The biggest issue is on the SPACES table - since it's used to look up content, blog posts, etc. So it's either a unique lookup on SPACES, or a table space scan to find 1 row.

We also saw this issue on LINKS.
select outgoingli0_.LINKID as LINKID, outgoingli0_.DESTPAGETITLE as DESTPAGE2_
, outgoingli0_.DESTSPACEKEY as DESTSPAC3_, outgoingli0_.CONTENTID as CONTENTID
, outgoingli0_.CREATOR as CREATOR, outgoingli0_.CREATIONDATE as CREATION6_
, outgoingli0_.LASTMODIFIER as LASTMODI7_, outgoingli0_.LASTMODDATE as LASTMODD8_
from LINKS outgoingli0_ CARD 1457006 NPAGES 57402
where (lower(outgoingli0_.DESTSPACEKEY)=? ) COLCARD 11320
and(lower(outgoingli0_.DESTPAGETITLE)=? ) COLCARD 356000

BLOGPOST Title also has the issue.

select blogpost0_.CONTENTID as CONTENTID, blogpost0_.SPACEID as SPACEID
, blogpost0_.TITLE as TITLE, blogpost0_.VERSION as VERSION
, blogpost0_.CREATOR as CREATOR, blogpost0_.CREATIONDATE as CREATION6_
, blogpost0_.LASTMODIFIER as LASTMODI7_, blogpost0_.LASTMODDATE as LASTMODD8_
, blogpost0_.VERSIONCOMMENT as VERSIONC9_, blogpost0_.PREVVER as PREVVER
, blogpost0_.CONTENT_STATUS as CONTENT11_
from CONTENT blogpost0_
, SPACES space1_
where blogpost0_.CONTENTTYPE='BLOGPOST'
and ((lower(space1_.SPACEKEY)=?
and blogpost0_.SPACEID=space1_.SPACEID)
and(lower(blogpost0_.TITLE)=? )
and(blogpost0_.CREATIONDATE>=? )
and(blogpost0_.CREATIONDATE<? )
and(blogpost0_.PREVVER is null )
and(blogpost0_.CONTENT_STATUS='current' ))

CONTENT_LABEL table.
select distinct label0_.LABELID as LABELID
, label0_.NAME as NAME, label0_.OWNER as OWNER
, label0_.NAMESPACE as NAMESPACE, label0_.CREATIONDATE as CREATION5_
, label0_.LASTMODDATE as LASTMODD6_
from LABEL label0_ CARD 22253 NPAGES 424
, CONTENT_LABEL labelling1_ CARD 73483 NPAGES 1711
where (lower(labelling1_.SPACEKEY)=? ) COLCARD 7813
AND(labelling1_.LABELID=label0_.LABELID ) COLCARD 22253/22253
AND(label0_.NAMESPACE=? ) COLCARD 4
order by label0_.NAME

ATTACHMENTS table.
SELECT attachmentid, title, contenttype, creator, creationdate
, lastmodifier, lastmoddate, filesize, attachment_comment
, attversion, prevver
FROM WIKI_RW.attachments CARD 403449
where pageid = ? COLCARD 94288
and lower(title) = ? COLCARD 253952
order by attversion desc with UR

Our wiki has a lot of content, a lot of attachements, a lot of spaces. Any big site - this issue is an absolute performance killer . The wiki quickly becomes useless.

We took the risk and we deployed the patch to production yesterday. So far so good

The data was manually migrated using SQL, the queries should be in the comment section of the patch.

Thanks for the cluster update. I thought that just a simple serialization was used to push the objects over the wire to the second node, but I wasn't sure.

I'd prefer the solution proposed in the patch over changing the hibernate queries and exploiting MySQL insensitivity because the solution is the smallest common denominator for all of your supported databases, which would make it easier for you to test and support it.

Using this solution you could easily and safely provide this enhancements to all of your customers regardless of their database via an upgrade task (I can image that creating an upgrade task that would create custom indexes for all the supported databases would be much trickier).

The benefit for us is that you'll do all your QA with the same schema as we use, minimizing the chance of something breaking for us in the future because of this change.

/i