Fast MySql REGEXP LIKE Search Solution

Recently had the job of improving the speed of a clients PHP script MySql query which used REGEXP (RLIKE)

The table was large >50k rows and negative matches needed to be used, the original query was not written using FullText Match Against Boolean due to the over 50% threshold limit and the Stopwords would cause poor results.

As we know LIKE with wildcards are pretty fast but no use for exact word searches, example %build% would find builder and building as well as build, so the query was written as REGEXP [[:<:]]build[[:>:]] which matches End Of Words.

Most queries took >6 seconds which was simply pathetic, so how do we speed this up?

Simply use LIKE  AND REGEXP, this way we only perform the REGEXP on the LIKE’ results

SELECT * FROM table WHERE field REGEXP '[[:<:]]build[[:>:]]' AND field NOT REGEXP '[[:<:]]material[[:>:]]'
6.210 Seconds

SELECT * FROM table WHERE field LIKE '%build%' AND field NOT LIKE '%material%'

0.244 Seconds

And Now

SELECT * FROM table WHERE field LIKE '%build%' AND field NOT LIKE '%material%' AND field REGEXP '[[:<:]]build[[:>:]]' AND field NOT REGEXP '[[:<:]]material[[:>:]]'

0.285 Seconds

REGEXP results at LIKE Speeds.

Let me know if this works for you.