[CAY-1210] mysql does not use index for case insensitive searches

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.1M3
• Component/s: Database integration
• Assignee: Ari Maniatis
• Reporter: Ari Maniatis
• Created: 24/Apr/09 00:47
• Updated: 28/Jan/14 10:47
• Resolved: 28/Jan/14 10:47

Description

When performing a case insensitive search Cayenne spits out SQL which looks like this

SELECT .... WHERE upper(name) LIKE upper("fred")

This prevents any index being used for the search. Since mysql already performed case insensitive searches on text fields we need to suppress the 'upper' functions being used in these situations. All searches on these fields are already case insensitive.

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html