CODEWITHSUNDEEP
mysqlleft-join
Santiago
Santiago

Reputation: 2465

DELETE using LEFT JOIN with LIMIT in MySQL

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

Answers (1)

Zane Bien
Zane Bien

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

Related Questions