Mysql regexp performance question

Question

Rumour has it that this;

SELECT * FROM lineage_string where lineage like '%179%' and  lineage regexp '(^|/)179(/|$)'

Would be faster than this;

SELECT * FROM lineage_string where lineage regexp '(^|/)179(/|$)'

Can anyone confirm ? Or know a decent way to test the speed of such queries. Thanks

Answer 1

It is possible that it could be faster because the LIKE condition can be evaluated more quickly then the regular expression so if most rows fail the test it could be faster. However it will be slower if most rows succeed as two tests must be run for successful rows instead of just one. It also depends on which expression the optimizer chooses to run first.

An even bigger speedup can be witnessed if you have something like this:

SELECT * FROM (
   SELECT * FROM lineage_string
   WHERE lineage LIKE '179%'
) WHERE lineage regexp '^179(/|$)'

Now an index can be used to find likely rows because LIKE '179%' is sargable. Many rows won't need to be checked at all.

As always the best way to be sure is to measure it for yourself on your actual data.

Answer 2

Yeah, it probably would be a tiny bit faster because standard-SQL LIKE is a simpler comparison operation than a full-on regex parser.

However, in real terms both are really slow, because neither can use indices. (LIKE can use an index if the match string doesn't start with a wildcard, but that's not the case here.)

If you are concerned about speed, you should change your schema so that you can put the 179 directly in a column and index it, rather than having to check through a string manually on every row.