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: vegiv...@gmail.com [mailto:vegiv...@gmail.com] 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 > <anan...@gmail.com<mailto:anan...@gmail.com>> 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.pr...@vt.edu<mailto:randall.pr...@vt.edu>>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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
