Bug #14712 map LEFT() to LIKE so indexes can be used
Submitted: 7 Nov 2005 17:08 Modified: 14 Jun 2013 18:19
Reporter: Carl Longnecker Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version: OS:Any (any)
Assigned to: Matthew Lord CPU Architecture:Any

[7 Nov 2005 17:08] Carl Longnecker
Description:
in certain situations, LEFT() can be mapped to LIKE so that indexes can be used. for example:

select artist
  from artists
 where left(prefix, 1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')

can be converted to:

select artist
  from artists
 where artist like '1%'
    or artist like '2%'
    or artist like '3%'
    or artist like '4%'
    or artist like '5%'
    or artist like '6%'
    or artist like '7%'
    or artist like '8%'
    or artist like '9%'

resulting in the use of an index

How to repeat:
none

Suggested fix:
none
[14 Jun 2013 18:19] Matthew Lord
Hi Carl!

Thank you for the feature request, and for your help in making MySQL even better!

I'm closing this old feature request for now, as we will be providing an alternative method for using an index here which will be even better than the one proposed.

Thanks again!