delete multiple rows in mysqldb-CodePudding

How can we optimize the delete query.

delete FROM student_score
    WHERE lesson_id IS NOT null
      AND id NOT IN(SELECT MaxID FROM temp)
ORDER BY id
LIMIT 1000

This select statement return "SELECT MaxID FROM temp" 35k lines and temp is a temporary table.

and select * FROM student_score WHERE lesson_id IS NOT null return around 500k rows

I tried using limit and order by clause but doesn't result in faster ways

CodePudding user response：

IN(SELECT...)` is, in many situations, really inefficient.

Use a multi-table DELETE. This involves a LEFT JOIN ... IS NULL, which is much more efficient.

Once you have mastered that, you might be able to get rid of the temp and simply fold it into the query.

Also more efficient is

WHERE NOT EXISTS ( SELECT 1 FROM temp
       WHERE student_score.lesson_id = temp.MAXID )

Also, DELETEing a large number of rows is inherently slow. 1000 is not so bad; 35K is. The reason is the need to save all the potentially-deleted rows until "commit" time.

Other techniques for big deletes: http://mysql.rjweb.org/doc.php/deletebig

Note that one of then explains a more efficient way to walk through the PRIMARY KEY (via id). Note that your query may have to step over lots of ids that have lesson_id IS NULL. That is, the LIMIT 1000 is not doing what you expected.

CodePudding user response：

You can do it without order by :

DELETE FROM student_score
WHERE lesson_id IS NOT null
AND id NOT IN (SELECT MaxID FROM temp)

Or like this using left join which is more optimized in term of speed :

DELETE s
FROM student_score s
LEFT JOIN temp t1 ON s.id = t1.MaxID 
WHERE lesson_id IS NOT null and t1.MaxID is null;