MySQL regexp is much slower than

Question

SELECT data FROM test WHERE col REGEXP "asdf_[0-9]+" LIMIT 1 

... 1 line in the set (1 min. 43.12 sec.)

 SELECT data FROM test WHERE col LIKE "asdf_%" LIMIT 1 

... 1 line in the set (0,01 sec)

regexp can give me the exact result, I need to filter the data if I use like sql. Is there any way to improve?

BTW: The test has 2 million lines and is growing.

Answer 1

Try changing the regexp line to "^asdf_[0-9]+" .

LIKE bound (i.e. LIKE 'asdf_%' says “line starting with asdf_”), whereas regexp not ( REGEXP 'asdf_[0-9]+' searches for it anywhere in the line).

Note that executing REGEXP 'asdf_[0-9]+' similar to saying LIKE '%asdf_%' .

I think regexp will still be a bit slower than LIKE , but hopefully the start of the linear anchor will speed it up significantly.