List:General Discussion« Previous MessageNext Message »
From:Randall Price Date:March 18 2010 2:03pm
Subject:RE: Question about DELETE
View as plain text  
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?


-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

Question about DELETERandall Price17 Mar
  • Re: Question about DELETEAnanda Kumar18 Mar
    • Re: Question about DELETEJohan De Meersman18 Mar
      • RE: Question about DELETERandall Price18 Mar
        • RE: Question about DELETEIan Simpson18 Mar
          • RE: Question about DELETERandall Price18 Mar
            • Re: Question about DELETEAnanda Kumar18 Mar
              • RE: Question about DELETERandall Price18 Mar