1

I wrote a query that finds me records in discovery table that contain a needle from whitelisted (LIKE %needle%) and don't have a record in logs table (and some other easy to understand filtering):

SELECT * FROM (
    SELECT discovery.id, discovery.resource AS url, discovery.event_datetime, whitelisted.keyword 
    FROM discovery
    INNER JOIN whitelisted
    ON discovery.`resource` LIKE concat('%', whitelisted.`keyword`, '%')
    LEFT JOIN `logs`
    ON `logs`.discovery_id = discovery.id
    WHERE
    discovery.event_datetime >= NOW()
    AND
    discovery.provider = 'XXX'
    AND
    `logs`.id IS NULL
    AND
    discovery.resource NOT LIKE '%SOMETHING-TO-EXCLUDE%'
    LIMIT 0, 20
) logless_resources
GROUP BY logless_resources.url
ORDER BY logless_resources.event_datetime ASC

discovery table: 143k rows

  • id UNSIGNED INT AI PRIMARY
  • resource VARCHAR(1024) INDEX
  • provider ENUM
  • event_datetime DATETIME
  • created_at DATETIME

whitelisted table: 535 rows (not thousands, just 0.5k)

  • id UNSIGNED INT AI PRIMARY
  • keyword VARCHAR(128) INDEX
  • provider ENUM
  • created_at DATETIME

logs: 585k rows

  • id UNSIGNED INT AI PRIMARY
  • discovery_id UNSIGNED INT FK to discovery.id

Right now this takes 90 seconds - can it be made faster?

  • 2
    SELECT * ... GROUP BY field produces random, non-deterministic, result, when field is not unique, so it makes no sense. Otherwise, if field is unique, the GROUP BY is excess, so it makes no sense again. – Akina Apr 15 at 5:20
  • 1
    ON discovery.`resource` LIKE concat('%', whitelisted.`keyword`, '%') produces a fullscan. So LEFT JOIN .. WHERE .. IS NULL is less effective than WHERE NOT EXISTS. – Akina Apr 15 at 5:23
  • 1
    +1 for a very good first question. <nitpick> it might be better to show your DDL as the results of SHOW CREATE TABLE blah\G?<nitpick> – Vérace Apr 15 at 10:01
  • @Akina I need the url in the resultset to be unique, hence the GROUP BY. I understand your rationale, but I did see a difference with and without GROUP BY. I also tried replacing LEFT JOIN .. WHERE .. IS NULL with WHERE NOT EXISTS as per your suggestion and found it to be more than twice as slow, unless I did it wrong (gist.github.com/NinoSkopac/746ff4fc38696e747923c6c7f3d6a640). I do appreciate your help tho. – Nino Škopac Apr 15 at 23:54
  • @Vérace I've never heard about SHOW CREATE TABLE, but I've tried it now and it's pretty cool - thanks man. – Nino Škopac Apr 15 at 23:56
1

The focus needs to be on the "derived" table, since it delivers only 20 rows to the outer parts.

        SELECT  d.id, d.resource AS url, d.event_datetime,
                wl.keyword
            FROM  discovery AS d
            INNER JOIN  whitelisted AS wl
                    ON d.`resource` LIKE concat('%', wl.`keyword`, '%')
            LEFT JOIN  `logs`  ON `logs`.discovery_id = d.id
            WHERE  d.event_datetime >= NOW()
              AND  d.provider = 'XXX'
              AND  `logs`.id IS NULL
              AND  d.resource NOT LIKE '%SOMETHING-TO-EXCLUDE%'
            LIMIT  0, 20 

Starting with the WHERE clause...

              d.event_datetime >= NOW() -- last in index (range)
              d.provider = 'XXX'  -- first in the index because of "="
              `logs`.id IS NULL   -- another table, not very selective
              d.resource NOT LIKE '%SOMETHING-TO-EXCLUDE%'  -- probably useless

So discovery needs INDEX(provider, event_datetime), in that order.

Now to get to the other tables

ON `logs`.discovery_id = d.id

Logs needs INDEX(discovery_id)

ON d.`resource` LIKE concat('%', wl.`keyword`, '%')

This is terrible for optimizing. It will need to test every row of either whitelisted or discovery. Since I have guessed that discovery will be the first table, I expect a tedious scan of whitelisted.

Do you care that there is no ORDER BY? That is, you could be getting a random 20 rows, not the newest 20, not the oldest, etc.

(Please use SHOW CREATE TABLE when presenting a schema; it is much more precise than prose.)

  • Hey Rick, thanks for your input. I went through your response and my main takeaway was that using ON MATCH() would be quicker, but I don't wanna fetch all keywords first (word 1 word2 ...) because IMO that'd entail using another query. I already have those indexes. I most certainly do care about the ORDER BY, that's why the "derived" table is in a subquery, so I can only get the first 20 rows. I've ended up ditching the LIMIT clause and just doing the query less frequently, and queing the processing of the results internally in PHP. thanks for the SHOW CREATE TABLE. – Nino Škopac Apr 30 at 22:52
  • @NinoŠkopac - Oops, I missed the NOT. So, I removed my FULLTEXT recommendation. – Rick James May 1 at 2:12
1

When you have a query that has multiple criteria on the same table a compound index is needed. MySQL will only use a single index for a table (or alias) so make sure its the right one.

First look at discovery since its on the left of the join. Start with items referenced and then ranges. So:

  • provider
  • event_datetime

Elements like resource because of the LIKE %x% expression can't use and index. For a small table we relay that (provider,event_datetime) restruct the table small enough to be be significant about including resource in the index.

So:

CREATE INDEX provider_event_datetime ON discovery (provider,event_datetime);

Note: is whitelisted.provider need to be the same as the discovery_provider?

A limit on the inner query is quite unpredictable without an ORDER BY.

The remaining join on logs is looking it up by the PK. No additional indexing is required there.

Look at EXPLAIN {query} to examine what indexes it is using and how.

  • Hey Dan, I ran the query and which added two indexes, which reduced the query time from 90 to 80 seconds on average. "is whitelisted.provider need to be the same as the discovery_provider?" - I don't quite understand that. What is PK? Here's my EXPLAIN query: docs.google.com/spreadsheets/d/… – Nino Škopac Apr 16 at 0:34
  • "is whitelisted.provider need to be the same as the discovery_provider?" I was wondering if you missed a 'JOIN ON whitelisted.provider = discovery.provider` in your question. PK -> primary key. Since it results in a BKA on the LIKE whitelist are large consider increasing join_buffer_size to see if it has an effect. – danblack Apr 16 at 1:02
  • Tried increasing join_buffer_size to 1.5GB, it didn't affect the query time. Thanks for your effort, I gave you a +1 :) – Nino Škopac Apr 16 at 2:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.