Mysql multiple OR DO NOT LIKE
I have a wordpress plugin that essentially creates a mysql query and returns the results in wordpress.
It is user driven and therefore can end up in large queries with multiple NOT LIKEs, resulting in a very slow query.
Any suggestions I could use to improve:
SELECT field1,field2,field3,field4
from datatable
WHERE (title NOT LIKE '%word%' AND title NOT LIKE '%word2%'
AND title NOT LIKE '%word3%' AND title NOT LIKE '%word4%'
AND title NOT LIKE '%word5%' AND title NOT LIKE '%word6%'
AND title NOT LIKE '%word7%' AND title NOT LIKE '%word8%'
AND title NOT LIKE '%word9%')
AND MATCH (title) AGAINST ("\"brandname\" " IN BOOLEAN MODE)
ORDER BY total ASC LIMIT 0,60
The client adds a lot of negative keywords to the wordpress plugin, which leads to larger queries than the one above.
+3
source to share
2 answers
This is easiest to do with REGEXP
. For multiple words, use a group like(one|two|three)
SELECT
field1,
field2,
field3,
field4
from datatable
WHERE
title NOT REGEXP '(word1|word2|word3|word4|word5...|word9)'
AND MATCH (title) AGAINST ("\"brandname\" " IN BOOLEAN MODE)
ORDER BY total ASC
LIMIT 0,60
+4
source to share
You can use the operation REGEXP
to compare all patterns at the same time.
Your request will look something like this:
SELECT field1,field2,field3,field4
FROM data table
WHERE title NOT REGEXP '^word[0-9]?$'
AND MATCH(title) ("\"brandname\" " IN BOOLEAN MODE)
ORDER BY total ASC LIMIT 0,60
+1
source to share