sql - MySQL regexp much slow than like -

this question has answer here:

select data    test   col regexp "asdf_[0-9]+"   limit 1

...1 row in set (1 min 43.12 sec)

select data    test   col "asdf_%"   limit 1

...1 row in set (0.01 sec)

regexp can give me exact result, have filter data if use like sql. there way improve?

btw: test has 2 million rows , grow up.

try changing regexp string "^asdf_[0-9]+".

like anchored (ie like 'asdf_%' says "a string starting asdf_"), whereas regexp not (regexp 'asdf_[0-9]+' looks anywhere within string).

note doing regexp 'asdf_[0-9]+' saying like '%asdf_%'.

i think regexp still bit slower like, start of line anchor speed up.

OSX