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?
From: Ananda Kumar [mailto:anandkl@stripped]
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.
On Thu, Mar 18, 2010 at 8:22 PM, Price, Randall
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.
From: Ian Simpson [mailto:ian@stripped<mailto:ian@stripped>]
Sent: Thursday, March 18, 2010 10:11 AM
To: Price, Randall
Cc: Johan De Meersman; Ananda Kumar; [MySQL]
Subject: RE: Question about DELETE
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?
> -Randall Price
> From: vegivamp@stripped<mailto:vegivamp@stripped>
> [mailto:vegivamp@stripped<mailto:vegivamp@stripped>] On Behalf Of Johan De
> 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
> 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.
> On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall
> > 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