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?
SELECT * ... GROUP BY field
produces random, non-deterministic, result, whenfield
is not unique, so it makes no sense. Otherwise, iffield
is unique, theGROUP BY
is excess, so it makes no sense again. – Akina Apr 15 '19 at 5:20ON discovery.`resource` LIKE concat('%', whitelisted.`keyword`, '%')
produces a fullscan. SoLEFT JOIN .. WHERE .. IS NULL
is less effective thanWHERE NOT EXISTS
. – Akina Apr 15 '19 at 5:23<nitpick>
it might be better to show your DDL as the results ofSHOW CREATE TABLE blah\G
?<nitpick>
– Vérace Apr 15 '19 at 10:01url
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 replacingLEFT JOIN .. WHERE .. IS NULL
withWHERE 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 '19 at 23:54SHOW CREATE TABLE
, but I've tried it now and it's pretty cool - thanks man. – Nino Škopac Apr 15 '19 at 23:56