Question about DELETE

Question about DELETE

am 17.03.2010 20:51:35 von Randall.Price

--_000_9269AB049ED62741A28FBF28706E31D325975C1ED2fangornccw2 kv_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 =3D 1 AND id =3D 5 ... AND ID =3D 100) how ma=
ny times are the foreign keys/indexes updated?

Once for the entire DELETE operation or one time for each record that is de=
leted?

Thanks,

Randall Price


--_000_9269AB049ED62741A28FBF28706E31D325975C1ED2fangornccw2 kv_--

Re: Question about DELETE

am 18.03.2010 06:05:24 von Ananda Kumar

--001636e1fd3fb5ade104820c2d09
Content-Type: text/plain; charset=ISO-8859-1

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 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
>
>

--001636e1fd3fb5ade104820c2d09--

Re: Question about DELETE

am 18.03.2010 11:47:59 von Johan De Meersman

--0016e646537ae61b30048210f63b
Content-Type: text/plain; charset=ISO-8859-1

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 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 > >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

--0016e646537ae61b30048210f63b--

RE: Question about DELETE

am 18.03.2010 15:03:55 von Randall.Price

--_000_9269AB049ED62741A28FBF28706E31D325975C1FECfangornccw2 kv_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 BET=
WEEN 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 BET=
WEEN 1 AND 500) it may take upwards of an hour or more.

So what would cause this increased slowness the more records you delete, un=
less the indexing is happening multiple times?

Thanks,

-Randall Price


From: vegivamp@gmail.com [mailto:vegivamp@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 rebuil=
t only once. I'm not entirely sure what happens to cascaded deletes, though=
..

On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar ndkl@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 o:Randall.Price@vt.edu>>wrote:

> Hello,
>
> I have a simple question about deleting records from INNODB tables. I ha=
ve
> 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 =3D 1 AND id =3D 5 ... AND ID =3D 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

--_000_9269AB049ED62741A28FBF28706E31D325975C1FECfangornccw2 kv_--

RE: Question about DELETE

am 18.03.2010 15:10:40 von Ian Simpson

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@gmail.com [mailto:vegivamp@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 > 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 >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=gcdmg-mysql-2@m.gmane.org

RE: Question about DELETE

am 18.03.2010 15:52:08 von Randall.Price

SSBoYXZlIHRoZSBNeVNRTCBBZG1pbmlzdHJhdG9yIHJ1bm5pbmcgYW5kIG9u IHRoZSBTZXJ2ZXIg
Q29ubmVjdGlvbnMgbWVudSBvbiB0aGUgVGhyZWFkcyB0YWIgSSBjYW4gc2Vl IHRoZSB0aHJlYWQg
cnVubmluZyAoaS5lLiwgREVMRVRFIEZST00gdGFibGUgV0hFUkUgLi4uKS4g IEkgcmVmcmVzaCB0
aGlzIHRhYiBwZXJpb2RpY2FsbHkgdG8gc2VlIHdoYXQgc3RhZ2UgdGhlIHBy b2Nlc3MgaXMgaW4u
ICBJdCBkb2VzIG5vdCBkaXNwbGF5IGFueSBpbmZvcm1hdGlvbiBhYm91dCBy ZWJ1aWxkaW5nIGlu
ZGV4ZXMsIGp1c3QgdGhhdCBpcyBydW5uaW5nIHRoZSBERUxFVEUgcXVlcnku DQoNCklmIEkgdHVy
biB0aGUgREVMRVRFIEZST00gaW50byBhIFNFTEVDVCB0byBzZWUgaWYgaXQg dGFrZXMgYSBsb25n
IHRpbWUgdG8gc2VsZWN0IHRoZSByZWNvcmRzIHRvIGRlbGV0ZSwgaXQgcmV0 dXJucyBhbG1vc3Qg
aW5zdGFudGx5IHNvIE15U1FMIHNlZW1zIHRvIGJlIGFibGUgdG8gZmluZCB0 aGUgcmVjb3JkcyB0
byBkZWxldGUgcHJldHR5IGZhc3QuICBJIGFsc28gYXNzdW1lIHRoYXQgdHVy bmluZyB0aGUgREVM
RVRFIEZST00gaW50byBhIFNFTEVDVCBpcyBhIHJlYXNvbmFibGUgd2F5IHRv IGRldGVybWluZSB0
aGlzLg0KDQpXaGVuIEkgZG8gYSBtYXNzIGRlbGV0ZSBvbiB0aGUgcGFyZW50 IHRhYmxlIChpLmUu
LCBERUxFVEUgRlJPTSB0YWJsZSBXSEVSRSBpZCBCRVRXRUVOIDEgQU5EIDUw MCkgYWxsIEkgY2Fu
IHNlZSBpbiB0aGUgcHJvY2VzcyBsaXN0IGlzIHRoZSBERUxFVEUgcnVubmlu Zy4gIFRoZSBwYXJl
bnQgcmVjb3JkcyBhcmUgZGVsZXRlZCBhbmQgdGhlIENBU0NBRElORyBERUxF VEVTIHRoZW4gZGVs
ZXRlcyB0aGUgY2hpbGQgcmVjb3JkcyBpbiB0aGUgb3RoZXIgdHdvIGNoaWxk IHRhYmxlcy4gIFRo
ZSBwcm9jZXNzIGxpc3QgZG9lcyBub3Qgc2hvdyBhbnkgaW5mb3JtYXRpb24g YWJvdXQgZGVsZXRp
bmcgdGhlIGNoaWxkIHJlY29yZHMgdGhyb3VnaCB0aGUgQ0FTQ0FESU5HIERF TEVURVMsIGp1c3Qg
dGhhdCBpdCBpcyBkZWxldGluZyB0aGUgcGFyZW50IHJlY29yZHMuDQoNClRo ZSBwYXJlbnQgYW5k
IGJvdGggY2hpbGQgdGFibGVzIGhhdmUgbXVsdGlwbGUgaW5kZXhlcyBvbiB0 aGVtLCBwbHVzIHRo
ZSBGT1JFSUdOIEtFWSBDT05TVFJBSU5UUy4gIFNvIGFsbCB0aGUgaW5kZXhl cyBvbiB0aGUgcGFy
ZW50IHRhYmxlIG11c3QgYmUgcmVidWlsdCwgYWxsIHRoZSBpbmRleGVzIG9u IHRoZSB0d28gY2hp
bGQgdGFibGVzIG11c3QgYmUgcmVidWlsdCwgYW5kIChJIHN1c3BlY3QpIHRo ZSBmb3JlaWduIGtl
eSBjb25zdHJhaW50cyBtdXN0IGJlIHJlYnVpbHQgKG5vdCBzdXJlIGFib3V0 IHRoaXMpLg0KDQpJ
IGhhdmUgdHJpZWQgZHJvcHBpbmcgdGhlIGZvcmVpZ24ga2V5cyBhbmQgaW5k ZXhlcywgcGVyZm9y
bWluZyB0aGUgZGVsZXRlcywgdGhlbiByZWJ1aWxkaW5nIHRoZSBpbmRleGVz IGFuZCBmb3JlaWdu
IGtleXMuICBIb3dldmVyLCB0aGlzIHByb2Nlc3MgaXMgZXF1YWxseSBhcyBs b25nIChhbmQgbWF5
YmUgZXZlbiBsb25nZXIpIGJlY2F1c2UgZHJvcHBpbmcgdGhlIGZvcmVpZ24g a2V5cyBhbmQgaW5k
ZXhlcyB0YWtlcyBhIGxvbmcgdGltZSwgdGhlIGRlbGV0ZSBzZWVtcyB0byBn byBwcmV0dHkgZmFz
dCwgYW5kIHRoZW4gcmVidWlsZGluZyB0aGUgaW5kZXhlcyBhbmQgZm9yZWln biBrZXlzIHRoZW4g
dGFrZXMgYSBsb25nIHRpbWUuICBUaGlzIHRlY2huaXF1ZSBtYXkgYmUgYWxy aWdodCBmb3IgZGVs
ZXRpbmcgYSBsYXJnZSBudW1iZXIgb2YgcmVjb3JkcywgYnV0IGZvciBhIHNt YWxsIG51bWJlciBp
dCBzdGlsbCB0YWtlcyBhIGxvbmcgdGltZSB0byBkcm9wIGFuZCByZWJ1aWxk Lg0KDQpJIGhhdmUg
dHJpZWQgZGVsZXRpbmcgZnJvbSB0aGUgYm90dG9tIHVwIChpLmUuLCBkZWxl dGluZyB0aGUgY2hp
bGQgcmVjb3JkcyBmaXJzdCwgdGhlbiB0aGUgcGFyZW50IHJlY29yZHMpIHRv IHNlZSBpZiB0aGF0
IHdvdWxkIG1heWJlIGJ5cGFzcyB0aGUgRk9SRUlHTiBLRVkgcmVidWlsZCAo aWYgdGhlcmUgaXMg
YWN0dWFsbHkgYSByZWJ1aWxkIGZvciB0aGlzLCBub3Qgc3VyZSkgYW5kIHNw ZWVkIHVwIHRoZSBw
cm9jZXNzIGJ1dCBpdCBkb2VzIG5vdC4gIEl0IHN0aWxsIHRha2VzIGEgbG9u ZyB0aW1lIG9uIGEg
bGFyZ2UgbnVtYmVyIG9mIGRlbGV0ZXMuDQoNClNvIEkgYW0gYXQgYSBxdWFu ZGFyeSBhcyB0byBo
b3cgdG8gbWFrZSB0aGlzIGRlbGV0ZSBwcm9jZXNzIHBlcmZvcm0gYmV0dGVy Lg0KDQpUaGFua3Ms
DQoNCi1SYW5kYWxsIFByaWNlDQoNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdl LS0tLS0NCkZyb206
IElhbiBTaW1wc29uIFttYWlsdG86aWFuQGl0Lm15am9iZ3JvdXAuY28udWtd IA0KU2VudDogVGh1
cnNkYXksIE1hcmNoIDE4LCAyMDEwIDEwOjExIEFNDQpUbzogUHJpY2UsIFJh bmRhbGwNCkNjOiBK
b2hhbiBEZSBNZWVyc21hbjsgQW5hbmRhIEt1bWFyOyBbTXlTUUxdDQpTdWJq ZWN0OiBSRTogUXVl
c3Rpb24gYWJvdXQgREVMRVRFDQoNCkhpIFJhbmRhbGwsDQoNCklmIHlvdSdy ZSB0YWxraW5nIGFi
b3V0IHByb2Nlc3NlcyB0aGF0IGFyZSB0YWtpbmcgdGhhdCBsb25nLCB0aGVu DQpydW5uaW5nIFNI
T1cgUFJPQ0VTU0xJU1Qgc2V2ZXJhbCB0aW1lcyBkdXJpbmcgdGhlIG9wZXJh dGlvbiBzaG91bGQg
Z2l2ZQ0KeW91IGEgcm91Z2ggaWRlYSB3aGF0IGl0IGlzIGRvaW5nIGF0IGVh Y2ggc3RhZ2UuDQoN
CkFsc28sIGRvIHlvdSBoYXZlIGFuIGluZGV4IG9uIHRoZSBpZCBjb2x1bW4/ IEl0IGNvdWxkIGp1
c3QgYmUgdGFraW5nIGENCmxvbmcgdGltZSB0byBpZGVudGlmeSBhbGwgdGhl IHJvd3MgaXQgbmVl
ZHMgdG8gZGVsZXRlLg0KDQpPbiBUaHUsIDIwMTAtMDMtMTggYXQgMTA6MDMg LTA0MDAsIFByaWNl
LCBSYW5kYWxsIHdyb3RlOg0KPiBUaGFua3MgZm9yIHlvdXIgcmVzcG9uc2Vz IG9uIHRoaXMuDQo+
IA0KPiBIb3dldmVyLCBJIHN1c3BlY3QgdGhhdCB0aGUgaW5kZXhlcyBhcmUg YmVpbmcgcmVidWls
dCBvdmVyIGFuZCBvdmVyIGR1cmluZyB0aGUgbWFzcyBkZWxldGUgb3BlcmF0 aW9uLg0KPiANCj4g
SWYgSSBkZWxldGUgYSBzbWFsbCBudW1iZXIgb2YgcmVjb3JkcyAoaS5lLiwg REVMRVRFIEZST00g
dGFibGUgV0hFUkUgaWQgQkVUV0VFTiAxIEFORCA1KSBpdCBtYXkgb25seSB0 YWtlIGEgbWludXRl
IG9yIHNvLg0KPiANCj4gSWYgSSBkZWxldGUgYSBsYXJnZSBudW1iZXIgb2Yg cmVjb3JkcyAoaS5l
LiwgREVMRVRFIEZST00gdGFibGUgV0hFUkUgaWQgQkVUV0VFTiAxIEFORCA1 MDApIGl0IG1heSB0
YWtlIHVwd2FyZHMgb2YgYW4gaG91ciBvciBtb3JlLg0KPiANCj4gU28gd2hh dCB3b3VsZCBjYXVz
ZSB0aGlzIGluY3JlYXNlZCBzbG93bmVzcyB0aGUgbW9yZSByZWNvcmRzIHlv dSBkZWxldGUsIHVu
bGVzcyB0aGUgaW5kZXhpbmcgaXMgaGFwcGVuaW5nIG11bHRpcGxlIHRpbWVz Pw0KPiANCj4gVGhh
bmtzLA0KPiANCj4gLVJhbmRhbGwgUHJpY2UNCj4gDQo+IA0KPiBGcm9tOiB2 ZWdpdmFtcEBnbWFp
bC5jb20gW21haWx0bzp2ZWdpdmFtcEBnbWFpbC5jb21dIE9uIEJlaGFsZiBP ZiBKb2hhbiBEZSBN
ZWVyc21hbg0KPiBTZW50OiBUaHVyc2RheSwgTWFyY2ggMTgsIDIwMTAgNjo0 OCBBTQ0KPiBUbzog
QW5hbmRhIEt1bWFyDQo+IENjOiBQcmljZSwgUmFuZGFsbDsgW015U1FMXQ0K PiBTdWJqZWN0OiBS
ZTogUXVlc3Rpb24gYWJvdXQgREVMRVRFDQo+IA0KPiBHaXZlbiB0aGF0IE9Q IGlzIHRhbGtpbmcg
YWJvdXQgYSBzaW5nbGUgZGVsZXRlIHN0YXRlbWVudCwgSSdtIGdvbm5hIGJl IHZlcnkgc3VycHJp
c2VkIGlmIGhlIG1hbmFnZXMgdG8gc3F1ZWV6ZSBhbiBpbnRlcm1lZGlhdGUg Y29tbWl0IGluIHRo
ZXJlIDotKQ0KPiANCj4gRm9yIGEgc2luZ2xlLXN0YXRlbWVudCBkZWxldGUg b24gYSBzaW5nbGUg
dGFibGUsIHRoZSBpbmRleGVzIHdpbGwgYmUgcmVidWlsdCBvbmx5IG9uY2Uu IEknbSBub3QgZW50
aXJlbHkgc3VyZSB3aGF0IGhhcHBlbnMgdG8gY2FzY2FkZWQgZGVsZXRlcywg dGhvdWdoLg0KPiAN
Cj4gT24gVGh1LCBNYXIgMTgsIDIwMTAgYXQgNjowNSBBTSwgQW5hbmRhIEt1 bWFyIDxhbmFuZGts
QGdtYWlsLmNvbTxtYWlsdG86YW5hbmRrbEBnbWFpbC5jb20+PiB3cm90ZToN Cj4gSGksDQo+IEl0
IGRlcGVuZHMgaG93IGZyZXF1ZW50bHkgdXIgZG9pbmcgYSBjb21taXQuDQo+ IElmIHlvdSBoYXZl
IHdyaXR0ZW4gYSBwbHNxbCwgd2l0aCBsb29wIGFuZCBpZiB5b3UgY29tbWl0 IGFmdGVyIGVhY2gg
cm93IGlzDQo+IGRlbGV0ZWQsIHRoZW4gaXQgZ2V0IHVwZGF0ZSBmb3IgZWFj aCByb3cuIEVsc2Ug
aWYgeW91IGNvbW1pdCBhdCB0aGUgZW5kIHRoZQ0KPiBsb29wLCBpdCBjb21t aXRzIG9ubHkgb25j
ZSBmb3IgYWxsIHRoZSByb3dzIGRlbGV0ZWQuDQo+IA0KPiByZWdhcmRzDQo+ IGFuYW5ka2wNCj4g
T24gVGh1LCBNYXIgMTgsIDIwMTAgYXQgMToyMSBBTSwgUHJpY2UsIFJhbmRh bGwgPFJhbmRhbGwu
UHJpY2VAdnQuZWR1PG1haWx0bzpSYW5kYWxsLlByaWNlQHZ0LmVkdT4+d3Jv dGU6DQo+IA0KPiA+
IEhlbGxvLA0KPiA+DQo+ID4gSSBoYXZlIGEgc2ltcGxlIHF1ZXN0aW9uIGFi b3V0IGRlbGV0aW5n
IHJlY29yZHMgZnJvbSBJTk5PREIgdGFibGVzLiAgSSBoYXZlDQo+ID4gYSBt YXN0ZXIgdGFibGUg
d2l0aCBhIGZldyBjaGlsZCB0YWJsZXMgbGlua2VkIHZpYSBGb3JlaWduIEtl eSBjb25zdHJhaW50
cy4NCj4gPiAgRWFjaCB0YWJsZSBoYXMgc2V2ZXJhbCBpbmRleGVzIGFzIHdl bGwuDQo+ID4NCj4g
PiBNeSBxdWVzdGlvbiBpczogIGlmIEkgZGVsZXRlIG1hbnkgcmVjb3JkcyBp biBhIHNpbmdsZSBk
ZWxldGUgc3RhdGVtZW50DQo+ID4gKGkuZS4sIERFTEVURSBGUk9NIHRhYmxl IFdIRVJFIGlkID0g
MSBBTkQgaWQgPSA1IC4uLiBBTkQgSUQgPSAxMDApIGhvdyBtYW55DQo+ID4g dGltZXMgYXJlIHRo
ZSBmb3JlaWduIGtleXMvaW5kZXhlcyB1cGRhdGVkPw0KPiA+DQo+ID4gT25j ZSBmb3IgdGhlIGVu
dGlyZSBERUxFVEUgb3BlcmF0aW9uIG9yIG9uZSB0aW1lIGZvciBlYWNoIHJl Y29yZCB0aGF0IGlz
DQo+ID4gZGVsZXRlZD8NCj4gPg0KPiA+IFRoYW5rcywNCj4gPg0KPiA+IFJh bmRhbGwgUHJpY2UN
Cj4gPg0KPiA+DQo+IA0KPiANCj4gDQo+IC0tDQo+IEJpZXIgbWV0IGdyZW5h ZHluDQo+IElzIGFs
cyBtb3N0ZXJkIGJ5IGRlbiB3eW4NCj4gU3kgZGllJ3QgZHJpbmt0LCBpcyBl ZW5lIGt3ZXplbA0K
PiBIeSBkaWUndCBkcmlua3QsIGlzIHJhcyBlZW4gZXplbA0KDQoNCi0tIA0K SWFuIFNpbXBzb24N
ClN5c3RlbSBBZG1pbmlzdHJhdG9yDQpNeUpvYkdyb3VwDQoNCg==

Re: Question about DELETE

am 18.03.2010 16:14:36 von Ananda Kumar

--00504502ad5f5ec5c8048214b083
Content-Type: text/plain; charset=ISO-8859-1

delete will also cause the undo(before image) to be generated, in case u
want to rollback. This will also add up to the delete completion time.

After each mass delete, rebuild indexes to remove gaps in indexes(remove
fragmentatio in the index). This will improve next delete or select.

regards
anandkl
On Thu, Mar 18, 2010 at 8:22 PM, Price, Randall wrote:

> I have the MySQL Administrator running and on the Server Connections menu
> on the Threads tab I can see the thread running (i.e., DELETE FROM table
> WHERE ...). I refresh this tab periodically to see what stage the process
> is in. It does not display any information about rebuilding indexes, just
> that is running the DELETE query.
>
> If I turn the DELETE FROM into a SELECT to see if it takes a long time to
> select the records to delete, it returns almost instantly so MySQL seems to
> be able to find the records to delete pretty fast. I also assume that
> turning the DELETE FROM into a SELECT is a reasonable way to determine this.
>
> When I do a mass delete on the parent table (i.e., DELETE FROM table WHERE
> id BETWEEN 1 AND 500) all I can see in the process list is the DELETE
> running. The parent records are deleted and the CASCADING DELETES then
> deletes the child records in the other two child tables. The process list
> does not show any information about deleting the child records through the
> CASCADING DELETES, just that it is deleting the parent records.
>
> The parent and both child tables have multiple indexes on them, plus the
> FOREIGN KEY CONSTRAINTS. So all the indexes on the parent table must be
> rebuilt, all the indexes on the two child tables must be rebuilt, and (I
> suspect) the foreign key constraints must be rebuilt (not sure about this).
>
> I have tried dropping the foreign keys and indexes, performing the deletes,
> then rebuilding the indexes and foreign keys. However, this process is
> equally as long (and maybe even longer) because dropping the foreign keys
> and indexes takes a long time, the delete seems to go pretty fast, and then
> rebuilding the indexes and foreign keys then takes a long time. This
> technique may be alright for deleting a large number of records, but for a
> small number it still takes a long time to drop and rebuild.
>
> I have tried deleting from the bottom up (i.e., deleting the child records
> first, then the parent records) to see if that would maybe bypass the
> FOREIGN KEY rebuild (if there is actually a rebuild for this, not sure) and
> speed up the process but it does not. It still takes a long time on a large
> number of deletes.
>
> So I am at a quandary as to how to make this delete process perform better.
>
> Thanks,
>
> -Randall Price
>
>
> -----Original Message-----
> From: Ian Simpson [mailto:ian@it.myjobgroup.co.uk]
> Sent: Thursday, March 18, 2010 10:11 AM
> To: Price, Randall
> Cc: Johan De Meersman; Ananda Kumar; [MySQL]
> Subject: RE: Question about DELETE
>
> 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@gmail.com [mailto:vegivamp@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 > anandkl@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 > >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
>
>

--00504502ad5f5ec5c8048214b083--

RE: Question about DELETE

am 18.03.2010 16:21:18 von Randall.Price

--_000_9269AB049ED62741A28FBF28706E31D325975C206Ffangornccw2 kv_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Would wrapping the DELETE in a TRANSACTION improve the performance any?

Also, when you say to "after each mass delete, rebuilt the indexes..." woul=
d running OPTIMIZE TABLE tablename; be the way to do this, or how?

Thanks,

-Randall Price


From: Ananda Kumar [mailto:anandkl@gmail.com]
Sent: Thursday, March 18, 2010 11:15 AM
To: Price, Randall
Cc: Ian Simpson; Johan De Meersman; [MySQL]
Subject: Re: Question about DELETE

delete will also cause the undo(before image) to be generated, in case u wa=
nt to rollback. This will also add up to the delete completion time.

After each mass delete, rebuild indexes to remove gaps in indexes(remove fr=
agmentatio in the index). This will improve next delete or select.

regards
anandkl
On Thu, Mar 18, 2010 at 8:22 PM, Price, Randall o:Randall.Price@vt.edu>> wrote:
I have the MySQL Administrator running and on the Server Connections menu o=
n the Threads tab I can see the thread running (i.e., DELETE FROM table WHE=
RE ...). I refresh this tab periodically to see what stage the process is =
in. It does not display any information about rebuilding indexes, just tha=
t is running the DELETE query.

If I turn the DELETE FROM into a SELECT to see if it takes a long time to s=
elect the records to delete, it returns almost instantly so MySQL seems to =
be able to find the records to delete pretty fast. I also assume that turn=
ing the DELETE FROM into a SELECT is a reasonable way to determine this.

When I do a mass delete on the parent table (i.e., DELETE FROM table WHERE =
id BETWEEN 1 AND 500) all I can see in the process list is the DELETE runni=
ng. The parent records are deleted and the CASCADING DELETES then deletes =
the child records in the other two child tables. The process list does not=
show any information about deleting the child records through the CASCADIN=
G DELETES, just that it is deleting the parent records.

The parent and both child tables have multiple indexes on them, plus the FO=
REIGN KEY CONSTRAINTS. So all the indexes on the parent table must be rebu=
ilt, all the indexes on the two child tables must be rebuilt, and (I suspec=
t) the foreign key constraints must be rebuilt (not sure about this).

I have tried dropping the foreign keys and indexes, performing the deletes,=
then rebuilding the indexes and foreign keys. However, this process is eq=
ually as long (and maybe even longer) because dropping the foreign keys and=
indexes takes a long time, the delete seems to go pretty fast, and then re=
building the indexes and foreign keys then takes a long time. This techniq=
ue may be alright for deleting a large number of records, but for a small n=
umber it still takes a long time to drop and rebuild.

I have tried deleting from the bottom up (i.e., deleting the child records =
first, then the parent records) to see if that would maybe bypass the FOREI=
GN KEY rebuild (if there is actually a rebuild for this, not sure) and spee=
d up the process but it does not. It still takes a long time on a large nu=
mber of deletes.

So I am at a quandary as to how to make this delete process perform better.

Thanks,

-Randall Price


-----Original Message-----
From: Ian Simpson [mailto:ian@it.myjobgroup.co.uk co.uk>]
Sent: Thursday, March 18, 2010 10:11 AM
To: Price, Randall
Cc: Johan De Meersman; Ananda Kumar; [MySQL]
Subject: RE: Question about DELETE

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 durin=
g the mass delete operation.
>
> If I delete a small number of records (i.e., DELETE FROM table WHERE id B=
ETWEEN 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 B=
ETWEEN 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@gmail.com [mailto:vegivamp@gmai=
l.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 ve=
ry surprised if he manages to squeeze an intermediate commit in there :-)
>
> For a single-statement delete on a single table, the indexes will be rebu=
ilt only once. I'm not entirely sure what happens to cascaded deletes, thou=
gh.
>
> On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar nandkl@gmail.com>>> wrot=
e:
> Hi,
> It depends how frequently ur doing a commit.
> If you have written a plsql, with loop and if you commit after each row i=
s
> deleted, then it get update for each row. Else if you commit at the end t=
he
> loop, it commits only once for all the rows deleted.
>
> regards
> anandkl
> On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall lto:Randall.Price@vt.edu> 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 constrain=
ts.
> > 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 =3D 1 AND id =3D 5 ... AND ID =3D 100=
) how many
> > times are the foreign keys/indexes updated?
> >
> > Once for the entire DELETE operation or one time for each record that i=
s
> > 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


--_000_9269AB049ED62741A28FBF28706E31D325975C206Ffangornccw2 kv_--