List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:March 11 2010 9:40pm
Subject:RE: Very slow delete for Master / Child tables with millions of rows
View as plain text  
This isn't surprising, especially if you have foreign keys or indexes, as
each DELETE will cascade and require a rebuild of the indexes (just as an
INSERT does). 

Make sure that for each DELETE you are using LIMIT 1; if it's in a loop
(and you're not deleting via PK, but it's a good habit to get into and
won't hurt if using a PK).

A trick I've done, is create another column called "delete_me" or
something, then you loop through and set a flag to 1 for all records you
want deleted. Then at the end (or during the night via crontab or
something) you simply:

DELETE FROM foo WHERE delete_me = 1; 

(you may need to adjust other code to ignore any records that are set for
deletion in SELECTs)

Depending on your schema you might also be able to do something like this,
if you want to manage the foreign key deletes yourself.

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM foo WHERE delete_me = 1; 
DELETE FROM bar WHERE delete_me = 1; 
SET FOREIGN_KEY_CHECKS=1;

I thought there was a way to turn of indexes as well in a similar way.


http://dev.mysql.com/doc/refman/5.0/en/delete.html

Also look at the LOW_PRIORITY if you're using MYISAM tables and QUICK.

Try the trick of INSERTing into a NEW table and using RENAME instead of
DELETE all together.

Store the ID's you want to delete in another table (maybe even a
HEAP/MEMORY one), then:
DELETE FROM LargeTable USING LargeTable INNER JOIN TemporarySmallTable ON
LargeTable.ID = TemporarySmallTable.ID;

Another idea I just had that may work is to use a VIEW as your SELECT table
(where 'delete_me <> 1') then all your code points at the VIEW, and you can
delete from the real main table whenever you like, or just keep it for
archival purposes.

Consider OPTIMIZE TABLE to reclaim unused space and reduce file sizes when
done too.

> -----Original Message-----
> From: vegivamp@stripped [mailto:vegivamp@stripped] On 
> Behalf Of Johan De Meersman
> Sent: Thursday, March 11, 2010 7:43 AM
> To: Price, Randall
> Cc: mysql@stripped
> Subject: Re: Very slow delete for Master / Child tables with 
> millions of rows
> 
> If you really have to loop through the entire set deleting 
> record by record,
> I'm not surprised it's slow. Could you change your application to loop
> through the records doing "stuff" without deleting (maybe 
> even "do stuff" en
> masse), and afterwards do a mass delete ?
> 
> I also have a nagging suspicion (unfounded by any actual 
> experience, though)
> that it might be faster to cut the explicit relations and do the child
> deletes as a separate single-block execute, too. Easy enough 
> to test, I
> suppose.
> 
> 
> -- 
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
> 

Thread
Very slow delete for Master / Child tables with millions of rowsRandall Price11 Mar
  • Re: Very slow delete for Master / Child tables with millions of rowsJohan De Meersman11 Mar
    • RE: Very slow delete for Master / Child tables with millions of rowsDaevid Vincent11 Mar
  • Re: Very slow delete for Master / Child tables with millions of rowsKrishna Chandra Prajapati11 Mar