MySQL: need help making a query faster 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 &gt;= 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?
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.