Bug #27966 Optimize LOWER() away when collation is case insensitive
Submitted: 19 Apr 2007 23:00 Modified: 26 Apr 2007 10:06
Reporter: Nils Meyer Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any

[19 Apr 2007 23:00] Nils Meyer
Some applications using database abstraction use the LOWER() function to compare strings case insensitivly, lowercasing the to be compared value in the application. This seriously affects performance in MySQL, as you can't use expression/function indexes. 

How to repeat:
Create a table with char, varchar or text column, fill with loads of data. Then issue a select query:

/* This is fast, because it can use an index on charfield */
SELECT * FROM table WHERE charfield = 'somelowercasestring';

/* This is slow because it can't use an index when there is a function call involved */
SELECT * FROM table WHERE lower(charfield) = 'somelowercasestring';

Suggested fix:
Optimizer should check for ci collation and don't use lower() if no case sensitivity is required and an index on the column is present, therefore enabling index usage.
[26 Apr 2007 10:06] Valeriy Kravchuk
Thank you for a reasonable feature request.