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: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On > Behalf Of Johan De Meersman > Sent: Thursday, March 11, 2010 7:43 AM > To: Price, Randall > Cc: mysql@lists.mysql.com > 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
