Geek asks and answers

DELETE using LEFT JOIN with LIMIT in MySQL

I am trying to delete child records in my database and I 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 more than 7,000,000 records.

Since I cannot use LIMIT with the query, I tried this and actually worked, but I'm not sure if this is an effective solution, or if it can 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?

Thank!

+5
source share
2 answers

You cannot use LIMITdirectly in DELETE when you are referencing multiple tables at the same time, but you can get around this by closing what you want to delete as part of the subquery:

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
+9
source

- ?

DELETE post.*
    FROM foro_post AS post
    LEFT JOIN foro_thread AS thread USING(threadid)
    WHERE thread.threadid IS NULL
    AND post.postid < 
        (SELECT MAX(postid) + 500 FROM post)

MAX(postid) MIN() .

0

More articles:


All Articles