quickest way of deleting data from mysql

Question

i am doing this:

delete from calibration_2009 where rowid in
(SELECT rowid FROM `qcvalues`.`batchinfo_2009` where reporttime like "%2010%");

i need to have about 250k rows deleted.

why does it take such a long time? is there a quicker way to do this?

Answer 1

If reporttime is a DATETIME, use:

DELETE FROM calibration_2009 
 WHERE rowid IN (SELECT rowid 
                   FROM `qcvalues`.`batchinfo_2009` 
                  WHERE reporttime BETWEEN STR_TO_DATE('2010-01-01', '%Y-%m-%d') 
                                       AND STR_TO_DATE('2010-12-31', '%Y-%m-%d'))

Answer 2

Not sure if this is valid inn mysql but have you tried

delete from calibration_2009 a inner join qcvalues.batchinfo_2009 b on a.rowid = b.rowid where reporttime like '%2010%'

Alternatively if the year part is at a fixed position within reporttime try using substring and equal sign as opposed to using the LIKE statement

Answer 3

Depending on how many rows the inner select returns, you have to do that many comparisons per row in calibration_2009 to see if it has to be deleted.

If the inner select returns 250k rows, then you're doing up to 250k comparisons per row in calibration_2009 just to see if that row should be deleted.

I'd say a faster approach would be to add a column to calibration_2009, if at all possible, called to_be_deleted. Then update that table

UPDATE calibration_2009 SET to_be_deleted = EXISTS (
  SELECT 1 FROM `qcvalues`.`batchinfo_2009`
  WHERE `batchinfo_2009.rowid = calibration_2009.rowid AND batchinfo_2009.reporttime like "%2010%"
  );

That should be pretty quick if both tables are indexed by rowid AND reporttime in batchinfo_2009.

Then just

DELETE FROM calibration_2009 WHERE to_be_deleted = 1;

Then you can delete that new field, or leave it there for future updates.

Answer 4

DELETE  c
FROM    `qcvalues`.`batchinfo_2009` b
JOIN    calibration_2009 c
ON      c.rowid = b.rowid
WHERE   b.reporttime LIKE '%2010%';

You should have an index on calibration_2009 (rowid) for this to work fast.

I assume it's a PRIMARY KEY anyway, but you better check.