Why would a DELETE statement NOT delete in one pass all records that meet the conditions?

Viewed 103

I have this SQL statements that attempts to delete ALL duplicate rows that has one of its fields set to NULL. "Duplicate" means having same value in 4 fields only (col1,col4,col5,col6).

  DELETE
  FROM owndb.tbl1
  WHERE tbl1.id IN
    (
      SELECT *
      FROM
        (
          SELECT x2.id
          FROM
            owndb.tbl1 x2
            INNER JOIN tbl2 y2 ON x2.col5 = y2.id
            INNER JOIN tbl3 z2 ON x2.col1 = z2.id
            INNER JOIN
              (
                SELECT x1.id, x1.col1, x1.col2, z1.col3, x1.col4, x1.col5, y1.col6, 
                       COUNT(x1.col1), COUNT(x1.col4), COUNT(x1.col5), COUNT(y1.col6)
                FROM owndb.tbl1 x1
                  INNER JOIN tbl2 y1 ON x1.col5 = y1.id
                  INNER JOIN tbl3 z1 ON x1.col1 = z1.id
                GROUP BY x1.col1, x1.col5, x1.col4, y1.col6
                HAVING
                  COUNT(x1.col1)     > 1
                  AND COUNT(x1.col5) > 1
                  AND COUNT(x1.col4) > 1
                  AND COUNT(y1.col6) > 1
              ) AS dups
              ON
                x2.col1     = dups.col1
                AND x2.col5 = dups.col5
                AND x2.col4 = dups.col4
                AND y2.col6 = dups.col6
        ) AS ids
    )
    AND tbl1.col2 IS NULL
  • When run on a database with about 40 million rows in tbl1, the statement above indeed deletes most of the duplicates (about 0.5 million) but keeps leaving some undeleted. It takes about 10 minutes to execute.
  • When run again, it again deletes most of the leftover duplicates (about 80 thousand) but keeps leaving some undeleted. It again takes about 10 minutes to execute.
  • When run again, it again deletes most of the leftover duplicates but keeps leaving some undeleted. Again, about 10 minutes to execute.
  • And so on and so on... after about 20 such runs, all duplicates are finally deleted.

Why? Why would this DELETE statement not delete, in a single pass, all records that meet the conditions?

Suspecting some form of a timeout condition, I checked the value of MAX_EXECUTION_TIME. It is 0. The documentation says "The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are NOT enabled."

Also, looking at the logs, I see that about 5x rows are being examined:

# Query_time: 860.938912  Lock_time: 0.001816 Rows_sent: 0  Rows_examined: 195,651,505
# Query_time: 888.031845  Lock_time: 0.000881 Rows_sent: 0  Rows_examined: 195,679,037
# Query_time: 918.936984  Lock_time: 0.001823 Rows_sent: 0  Rows_examined: 195,647,462
# Query_time: 864.034052  Lock_time: 0.002571 Rows_sent: 0  Rows_examined: 195,641,058
# Query_time: 907.320618  Lock_time: 0.001008 Rows_sent: 0  Rows_examined: 195,645,355

What do I need to do in order for a single run to delete all such records, taking as long as it takes?

0 Answers
Related