79999

mysql multiple OR NOT LIKES

Question:

I have a wordpress plugin that essentially creates a mysql query and returns the results to wordpress.

It is user driven and so can end up in large queries with multiple NOT LIKEs which results in a very slow query.

Any suggestions that 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 customer is adding a lot of negative keywords to the wordpress plugin which results in larger queries than the one above.

Answer1:

This is most easily done with <a href="http://dev.mysql.com/doc/refman/5.1/en/regexp.html" rel="nofollow">REGEXP</a>. 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

Answer2:

You can use a <a href="http://dev.mysql.com/doc/refman/5.1/en/regexp.html" rel="nofollow">REGEXP</a> operation to compare all the patterns at once.

Your query will be something like:

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

Recommend

  • Check to see if a string matches a pattern
  • Collect distinct field names at nested level with specific condition
  • How to maintain uniqueness during SQL Join within Access-VBA function?
  • How to extract a subset of a database into a dbunit file using a sql query?
  • How to use generics properly for a Holder
  • DataTable Select Implementation with Multiple AND Conditions
  • How to compare two rows or get the fields which value is not match with the rows that compared?
  • Joining multiple tables in bigquery
  • inserting a new row instead of updating the current row
  • Modify Attribute of Wicket ComponentTag Parent
  • CompiledQuery with List.Contains (where…in list) functionality?
  • How to make an elasticsearch query that filters on the maximum value of a field?
  • How to improve this function?
  • Sorting is not working in jQuery datatables
  • Index on every Foreign Key?
  • Javascript and VERY LONG string
  • Primeng - Cant display array items in a column
  • JSF disable inputbox with a checkbox in a ui:repeat on a composite component
  • JHipster Entity Generator crahing
  • Scala Play form validation: different forms for one case class - is it possible?
  • JSF binding with setValueExpression read-only?
  • ValidationResult Returned From IValidatableObject.Validate Is Not Localized
  • Sorting a DataTable using LINQ, when sort-by-columns may vary
  • What is the JSF behaviour, if you bind the same backing bean property to two input fields in the sam
  • Order By Split Column
  • Displaying image retrieved from database to image control
  • How do I generate data for Google Visualizations on the server using WebSharper
  • use images instead of text in webgrid asp mvc
  • Gforce min not supported for character in data.table
  • distinct values from multiple fields within one table ORACLE SQL
  • Adding Parent and Child Nodes in TreeView from Sql Server 2008
  • Sending keystrokes/mouse clicks to a Java program with Autohotkey
  • How to match http request and response using Jersey ContainerRequestFilter and ContainerResponseFilt
  • Retrieving value from sql ExecuteScalar()
  • Different response to non-authenticated users and AJAX calls
  • Knitr HTML Loop - Some HTML output, some R output
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?