CODEWITHSUNDEEP
mysql
Donato
Donato

Reputation: 2777

speed up query with a lot of like clauses

I have a dockets table which has many entries. I already added an index to the foreign key of entries with key type of "MUL". But the queries still run slow because I have to check 2 columns for each row to check if they do not contain any of 4 keywords. That means I have 8 NOT LIKE clauses for this one query:

SELECT `entries`.* FROM `entries` 
WHERE `entries`.`docket_id` IN (1, ...) 
AND (column1 NOT LIKE '%Keyword1%' AND column2 NOT LIKE '%Keyword1%') 
AND (column1 NOT LIKE '%Keyword2%' AND column2 NOT LIKE '%Keyword2%') 
AND (column1 NOT LIKE '%Keyword3%' AND column2 NOT LIKE '%Keyword3%') 
AND (column1 NOT LIKE '%Keyword4%' AND column2 NOT LIKE '%Keyword4%')

Is there anything else I can do to speed up this query besides adding the index to the foreign key, which I have already done.

Upvotes: 1

Views: 102

Answers (3)

Andy Lester
Andy Lester

Reputation: 93636

MySQL cannot use any indexes on column1 or column2. It could if you were doing LIKE 'Keyword1%'. Since MySQL cannot use the indexes, it must check every row in the table to find the match.

Read more here: http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

Upvotes: 0

CargoMeister
CargoMeister

Reputation: 4299

If you really need to check that way, create a keywords table. Only two columns, the id from the entries table, and the keyword. Then check that table, except you don't need to do the likes, you can do an exact match.

Upvotes: 1

Devin H.
Devin H.

Reputation: 1105

Per Andy, this will not make it faster. However, it may help determine that the NOT LIKE statements are not the slow down culprit. I haven't tested this, but try something like this:

SELECT `entries`.* FROM `entries` 
WHERE `entries`.`docket_id` IN (1, ...) 
  AND (
     column1 NOT REGEXP '.*keyword1.*|.*keyword2.*|.*keyword3.*|.*keyword4.*'
     AND 
     column2 NOT REGEXP '.*keyword1.*|.*keyword2.*|.*keyword3.*|.*keyword4.*'
  )

https://dev.mysql.com/doc/refman/5.1/en/regexp.html

Upvotes: 0

Related Questions