What is an efficient way to do pattern match in MySQL?

Question

We have a huge pattern match in a query (about 12 strings to check). Now I have to do this query in MySQL, but it seems that the query from PostgreSQL has not the same syntax.

From PostgreSQL:

SELECT value  
FROM ...  
WHERE ...  
AND `value` !~* '.*(text|text2|text3|text4|text5).*';  

How do I do this in MySQL in a efficient way? I know, that this is probably not efficient at all. What is the most efficient way to do this?

This does the trick, but is probably the worst query possible to do this:

SELECT `value`  
FROM ...  
WHERE ...  

AND NOT (  
   `value` LIKE '%text%'  
OR `value` LIKE '%text2%'  
OR `value` LIKE '%text3%'  
OR `value` LIKE '%text4%'  
OR `value` LIKE '%text5%');  

Is REGEXP the way to go here? Then I'll have to figure out the corresponding expression.

Answer 1

Yes, REGEXP or its alternative spelling RLIKE:

WHERE value NOT RLIKE 'text|text2|text3|text4|text5'

(a regexp match is not anchored to the ends of the string unless you explicitly use ^ and $, so you don't need the .*(...).*.)

You should test it to see whether it is actually faster than the version with LIKE. On my test database with four words to find, LIKE was still considerably faster. It also has the advantage of being ANSI standard SQL so you can use it on any database.

If this is a common query, you should also consider fulltext indexing.