List:General Discussion« Previous MessageNext Message »
From:Ian Simpson Date:March 18 2010 2:10pm
Subject:RE: Question about DELETE
View as plain text  
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: 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:
> 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.Price@stripped<mailto:Randall.Price@stripped>>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

Thread
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