Thanks for your responses on this.
However, I suspect that the indexes are being rebuilt over and over during the mass delete
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?
From: vegivamp@stripped [mailto:vegivamp@stripped] 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
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
> 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
> 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