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

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

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

...1 row in set (0.01 sec)

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

BTW: test has 2 million rows and grow up.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
everbox
  • 1,189
  • 3
  • 16
  • 25

1 Answers1

4

Try changing your regexp string to "^asdf_[0-9]+".

LIKE is anchored (ie LIKE 'asdf_%' says "a string starting with asdf_"), whereas REGEXP is not (REGEXP 'asdf_[0-9]+' looks for that anywhere within the string).

Note that doing REGEXP 'asdf_[0-9]+' is like saying LIKE '%asdf_%'.

I think REGEXP will still be a bit slower than LIKE, but hopefully the start of line anchor will greatly speed it up.

mathematical.coffee
  • 55,977
  • 11
  • 154
  • 194
  • Slower... `1 row in set (1 min 52.61 sec)` – everbox Mar 09 '12 at 05:57
  • That's odd, I've never experienced the addition of a `^` *slowing* down a regex - sorry, can't help you then (try the full text search like @OMGPonies suggested, but I don't know enough about that to help you). – mathematical.coffee Mar 09 '12 at 06:04
  • Ok, re your clarification, try `^asdf_[0-9]+$` as your `regexp` and see if that speeds it up. – mathematical.coffee Mar 09 '12 at 06:16
  • You may also need to give more context - I just made a table filled with 2million text strings (albeit not very long), and both `LIKE 'asdf_%'` and `REGEXP '^asdf_[0-9]+'` (in combination with `LIMIT 1`) gave 0.01 sec. What sort of strings are in there (ie how long are they?)? what's the column type? etc. – mathematical.coffee Mar 09 '12 at 06:39