Reputation: 2465
I'm trying to delete orphan posts in my database and I have created this query:
DELETE post.*
FROM foro_post AS post
LEFT JOIN foro_thread AS thread USING(threadid)
WHERE thread.threadid IS NULL
The problem is that I want to limit because my table has over 7,000,000 records.
As I can't use LIMIT with the query, I tried this and actually worked, but I'm not sure if is an efficient solution or if it could be done better.
DELETE post.*
FROM foro_post AS post
LEFT JOIN foro_thread AS thread USING(threadid)
WHERE thread.threadid IS NULL
AND post.postid < 500
// Where < 500 should be increasing as I delete records
How can I do this more efficiently?
Upvotes: 19
Views: 20859
Reputation: 23135
You can't use LIMIT
directly within DELETE when you're referencing multiple tables at the same time, but you can get around that by encasing what you want to delete within a subselect:
DELETE po
FROM foro_post po
JOIN (
SELECT p.postid
FROM foro_post p
LEFT JOIN foro_thread t ON p.threadid = t.threadid
WHERE t.threadid IS NULL
ORDER BY p.postid
LIMIT 50
) pp ON po.postid = pp.postid
Upvotes: 33