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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.0
• OS: Any
• CPU Architecture: Any

[19 Apr 2007 23:00] Nils Meyer

Description:
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.