regex in Mysql select statement

Question

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 :

2012 that should match 2012 , 2012/* & 2012/*/*
2012/10 that should match 2012/10 & 2012/10/*
2012/10/3 that should match only 2012/10/3

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

Are your fields always valid dates or do they need to be validated? Ie would it be possible for a column to contain 2012isagoodyear and need to reject it? — Joachim Isaksson
– Joachim Isaksson, Commented Oct 2, 2012 at 21:33

Joachim Isaksson · Accepted Answer · 2012-10-02 22:02:32Z

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%').

Rush · Accepted Answer · 2012-10-02 20:57:11Z

0

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]+'

answered Oct 2, 2012 at 20:57

Rush

7254 silver badges13 bronze badges

Collectives™ on Stack Overflow

regex in Mysql select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related