MySQL query speed: REGEXP vs. LIKE

Posted by: Paul on: February 28, 2008

• In: databases | SQL
• 13 Comments

MySQL’s REGEXP function is handy when you need to search for specific text strings within a textual column, but over large record sets, it’s performance can be less than fantastic.

Recently I had a situation at work where I needed to do a first name / last name search across a single name field. Due to poor database design, the name field was in the form “lastname, first names”. Initially I used REGEXP to come up with something like this:

Select table1.some-data, table2.some-other-data
from
table1, table2
where
table1.name REGEXP "^[ a-z]{0,},[ a-z]{0,}'.$firstName.'[ a-z]{0,}$"
and
table1.name REGEXP "^[ a-z]{0,}'.$lastName.'[ a-z]{0,},[ a-z]{0,}$"
and
table1.key = table2.foreignKey

Our testing showed that this query produced a good quality result for name searches, but I had a gut feeling it would slow the database server considerably when under a bit of load.

Testing using JMeter confirmed this hunch and I was faced with the task of optimising the query without affecting the quality of the results.

What to do? What would give the pattern-matching punch of REGEXP without killing the server?

Enter the LIKE function. I’ve used LIKE before but didn’t realise how powerful (and fast) it was. After a bit of tweaking I cam up with the following:

Select table1.some-data, table2.some-other-data
from
table1, table2
where
table1.name LIKE "%,%'.$firstName.'%"
and
table1.name LIKE "%'.$lastName.'%,%"
and
table1.key = table2.foreignKey

I had (falsely) thought initially that LIKE‘s wild card characters (the “%” in the query) could only occur at the start or end of a query, but it turns out they can be used for all sorts of pattern matching goodness. The query using LIKE turned out the same results as the query using REGEXP, only it was at least 10 times faster.

The downside with LIKE‘s pattern matching (which didn’t affect my query) is that it’s wild card will match any character, whereas REGEXP can be tuned using normal regular expressions to providfe much more granular query control.

If you just need fast, ‘any character’ pattern matching though, LIKE is your friend.

Tags: mysql, optimisation, regexp-is-voodoo