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


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







All Articles