Ivan Ivan - 3 months ago 8
MySQL Question

How to make this delete query more efficient in MySQL?

I have this query, which runs in about 0.1 seconds in a table with over 100,000 records, which I think is good (d1 and d2 are indexed)

SELECT key_id
FROM `info`
WHERE d1 > 10 AND d1 < 20 AND d2 > 5 AND d2 < 15
GROUP BY `key_id`
HAVING COUNT(*) > 300;


Now I want to delete all these key_id from another table, so I tried this:

DELETE FROM `keys` WHERE id IN (<first query>)


The keys table has around 7,000 rows. I've read that the subquery evaluates everytime, so it's running 7000 times, 0.1 seconds each ~= 700 seconds. Terribly slow. Haven't let it finish tho, so I'm not sure if it takes 700 seconds. What I know for sure is that it takes more than 30 secs.

How can I improve this query?

Answer

Use a JOIN instead of subquery

DELETE keys
FROM keys
JOIN (<first query>) AS i
ON k.id = i.key_id