delete taking really long time! mysql

Question

i am doing this:

delete calibration_2009 from
calibration_2009 join batchinfo_2009
on calibration_2009.rowid = batchinfo_2009.rowid
where batchinfo_2009.reporttime like '%2010%';

both tables have about 500k lines of data i suspect that 250k match the criteria to be deleted

so far it has been running for 2 hours!!! is there something wrong?

dev.mysql.com says i should do this:

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

Select the rows not to be deleted into an empty table that has the same structure as the original table:

INSERT INTO t_copy SELECT * FROM t WHERE ... ;
Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:

RENAME TABLE t TO t_old, t_copy TO t;
Drop the original table:

DROP TABLE t_old;

how do i do this with my current statement?

Answer 1

Try adding an index for batchinfo_2009.reporttime and rowid also see if there are any table locks in case of using MyISAM

Answer 2

Some things to think about and try:

• Are you sure that rowid is your primary key?
• Are they the same data types?
• Do you have an index build on it?
• Wrap the delete in a transaction

• Confirm you don't have any active triggers from the delete.

• Can you post the table schema?

• Also, run the query through the Query Analyser to confirm the joins are happening properly

---

SCRIPT TO COPY TABLE:

INSERT INTO calibration_2009_copy
SELECT calibration_2009.* 
FROM calibration_2009 
 JOIN batchinfo_2009 
   ON calibration_2009.rowid = batchinfo_2009.rowid
WHERE batchinfo_2009.reporttime not like '%2010%';

RENAME TABLE calibration_2009 TO calibration_2009_old;
RENAME TABLE calibration_2009_copy TO calibration_2009;

DROP TABLE calibration_2009_old;

Answer 3

I don;t know about mysql but this '%2010%' would guarantee a table scan in SQl Server. That would certainly slow things down.

Answer 4

WHERE batchinfo_2009.reporttime NOT LIKE '%2010%';

This is probably the reason the query is so slow. Using LIKE, especially with a wildcard in the beginning of the string, will cause the query to not use an index on reporttime.

A better query would be:

DELETE calibration_2009
FROM calibration_2009
JOIN batchinfo_2009
ON calibration_2009.rowid = batchinfo_2009.rowid
WHERE batchinfo_2009.reporttime NOT BETWEEN MAKE_DATE(2010, 1) AND MAKE_DATE(2010, 365);
#`repporttime` is a TIMESTAMP

This would perform a range scan on repporttime using an index.