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
