Bug #747 using an index on some regular expressions
Submitted: 28 Jun 2003 6:44 Modified: 14 Jul 2003 16:57
Reporter: Daniel Penning Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version: OS:
Assigned to: Peter Gulutzan CPU Architecture:Any
Triage: D5 (Feature request)

[28 Jun 2003 6:44] Daniel Penning
Description:
regular expressions starting with constant characters and those starting with a small character range (e.g. [a-d].*) should be able to use an index to speed up the search.

while this performance improvement is not implemented a workarround should be mentionend into the documentation:

SELECT * FROM foo WHERE bar RLIKE 't[aeiou].*'
should be changed to
SELECT * FROM foo WHERE bar LIKE 't%' AND bar RLIKE 't[aeiou].*'

How to repeat:
-
[14 Jul 2003 16:57] Peter Gulutzan
There is another way to accomplish the same effect: use LIKE. If you have an indexed 
column column1, and you ask "explain select * from table1 where column1 LIKE 'd%'", 
you will see that the query does cause use of an index. 
 
However, we do intend to work toward a "faster REGEXP package". It's already on our list 
of possible future changes. When we start work (sorry, no dates), we will certainly regard 
this as a reasonable suggestion for the set of enhancements.
[9 May 2006 19:03] Mathew Johnston
Note that for the index to be used the regular expression would have to be "rooted" with a '^' (eg, '^somepattern') Without the '^', the regex is supposed to match anywhere within the entire string, not strictly from the beginning.
[8 Feb 2007 23:08] Mark Lehmann
Has any work been done to optimize anchored regexp queries since 2003?
[18 Feb 2007 18:56] Peter Gulutzan
For the specific question that was the subject of this feature request,
there has been no optimization. Please send questions about the product
to a mailing list or to a forum.
[29 Mar 2010 10:32] eliza sahoo
I also want to shar something similar to this.
http://www.mindfiresolutions.com/using-regular-expressions-with-mysql-queries-to-search-75...