CODEWITHSUNDEEP
mysqlregexperformance
Ariyan
Ariyan

Reputation: 15158

regex in Mysql select statement

I need to search for some user input patterns in database.
is it efficient to use Regex in Mysql SELECT statement's where clause ( in a scale of 10000 rows on an indexed column)?
My search is for dates and can be in these three forms :

Note: I have three columns that should be checked for this type of match separately.

Upvotes: 0

Views: 249

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181037

Although I can't find a definite reference in the MySQL documentation, due to the index structure, regex matches can not use index lookups.

They may scan indexes using the regex instead of scanning the full table, but any regex search will be more or less equivalent to a full table scan.

If your regex starts with a constant string, you may want to structure the query as;

SELECT * FROM TableA WHERE field LIKE 'tr%' AND field RLIKE 'tr[uae].*'

to get some index help from the LIKE that - if matched from the start of the string - can use the index to its advantage.

EDIT: Since your selects are fairly simple and match from the start of the string, you can just use LIKE to make them work, which will utilize indexes well; 

SELECT * FROM TableA WHERE field1='2012' OR field1 LIKE '2012/%'
SELECT * FROM TableA WHERE field1='2012/10' OR field1 LIKE '2012/10/%'
SELECT * FROM TableA WHERE field1='2012/10/3'

The reason to split the comparison into two is to avoid a search for 2012/1 that would match 2012/10 with just a LIKE that covers both cases (ie LIKE '2012/1%').

Upvotes: 2

Rush
Rush

Reputation: 715

That happens because of LIKE is not supposed to accept regular expression as a parameter. There is REGEXP for such things

WHERE field NOT REGEXP '%_[0-9]+'

Upvotes: 0

Related Questions