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] 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
> <anandkl@stripped<mailto:anandkl@stripped>> wrote:
> 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