Would wrapping the DELETE in a TRANSACTION improve the performance any? Also, when you say to "after each mass delete, rebuilt the indexes..." would running OPTIMIZE TABLE tablename; be the way to do this, or how?
Thanks, -Randall Price From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Thursday, March 18, 2010 11:15 AM To: Price, Randall Cc: Ian Simpson; Johan De Meersman; [MySQL] Subject: Re: Question about DELETE delete will also cause the undo(before image) to be generated, in case u want to rollback. This will also add up to the delete completion time. After each mass delete, rebuild indexes to remove gaps in indexes(remove fragmentatio in the index). This will improve next delete or select. regards anandkl On Thu, Mar 18, 2010 at 8:22 PM, Price, Randall <randall.pr...@vt.edu<mailto:randall.pr...@vt.edu>> wrote: I have the MySQL Administrator running and on the Server Connections menu on the Threads tab I can see the thread running (i.e., DELETE FROM table WHERE ...). I refresh this tab periodically to see what stage the process is in. It does not display any information about rebuilding indexes, just that is running the DELETE query. If I turn the DELETE FROM into a SELECT to see if it takes a long time to select the records to delete, it returns almost instantly so MySQL seems to be able to find the records to delete pretty fast. I also assume that turning the DELETE FROM into a SELECT is a reasonable way to determine this. When I do a mass delete on the parent table (i.e., DELETE FROM table WHERE id BETWEEN 1 AND 500) all I can see in the process list is the DELETE running. The parent records are deleted and the CASCADING DELETES then deletes the child records in the other two child tables. The process list does not show any information about deleting the child records through the CASCADING DELETES, just that it is deleting the parent records. The parent and both child tables have multiple indexes on them, plus the FOREIGN KEY CONSTRAINTS. So all the indexes on the parent table must be rebuilt, all the indexes on the two child tables must be rebuilt, and (I suspect) the foreign key constraints must be rebuilt (not sure about this). I have tried dropping the foreign keys and indexes, performing the deletes, then rebuilding the indexes and foreign keys. However, this process is equally as long (and maybe even longer) because dropping the foreign keys and indexes takes a long time, the delete seems to go pretty fast, and then rebuilding the indexes and foreign keys then takes a long time. This technique may be alright for deleting a large number of records, but for a small number it still takes a long time to drop and rebuild. I have tried deleting from the bottom up (i.e., deleting the child records first, then the parent records) to see if that would maybe bypass the FOREIGN KEY rebuild (if there is actually a rebuild for this, not sure) and speed up the process but it does not. It still takes a long time on a large number of deletes. So I am at a quandary as to how to make this delete process perform better. Thanks, -Randall Price -----Original Message----- From: Ian Simpson [mailto:i...@it.myjobgroup.co.uk<mailto:i...@it.myjobgroup.co.uk>] Sent: Thursday, March 18, 2010 10:11 AM To: Price, Randall Cc: Johan De Meersman; Ananda Kumar; [MySQL] Subject: RE: Question about DELETE Hi Randall, If you're talking about processes that are taking that long, then running SHOW PROCESSLIST several times during the operation should give you a rough idea what it is doing at each stage. Also, do you have an index on the id column? It could just be taking a long time to identify all the rows it needs to delete. On Thu, 2010-03-18 at 10:03 -0400, Price, Randall wrote: > Thanks for your responses on this. > > However, I suspect that the indexes are being rebuilt over and over during > the mass delete operation. > > If I delete a small number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 5) it may only take a minute or so. > > If I delete a large number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 500) it may take upwards of an hour or more. > > So what would cause this increased slowness the more records you delete, > unless the indexing is happening multiple times? > > Thanks, > > -Randall Price > > > From: vegiv...@gmail.com<mailto:vegiv...@gmail.com> > [mailto:vegiv...@gmail.com<mailto:vegiv...@gmail.com>] On Behalf Of Johan De > Meersman > Sent: Thursday, March 18, 2010 6:48 AM > To: Ananda Kumar > Cc: Price, Randall; [MySQL] > Subject: Re: Question about DELETE > > Given that OP is talking about a single delete statement, I'm gonna be very > surprised if he manages to squeeze an intermediate commit in there :-) > > For a single-statement delete on a single table, the indexes will be rebuilt > only once. I'm not entirely sure what happens to cascaded deletes, though. > > On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar > <anan...@gmail.com<mailto:anan...@gmail.com><mailto:anan...@gmail.com<mailto:anan...@gmail.com>>> > wrote: > Hi, > It depends how frequently ur doing a commit. > If you have written a plsql, with loop and if you commit after each row is > deleted, then it get update for each row. Else if you commit at the end the > loop, it commits only once for all the rows deleted. > > regards > anandkl > On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall > <randall.pr...@vt.edu<mailto:randall.pr...@vt.edu><mailto:randall.pr...@vt.edu<mailto:randall.pr...@vt.edu>>>wrote: > > > Hello, > > > > I have a simple question about deleting records from INNODB tables. I have > > a master table with a few child tables linked via Foreign Key constraints. > > Each table has several indexes as well. > > > > My question is: if I delete many records in a single delete statement > > (i.e., DELETE FROM table WHERE id = 1 AND id = 5 ... AND ID = 100) how many > > times are the foreign keys/indexes updated? > > > > Once for the entire DELETE operation or one time for each record that is > > deleted? > > > > Thanks, > > > > Randall Price > > > > > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel -- Ian Simpson System Administrator MyJobGroup
