From: Zhangzhigang Date: May 7 2012 9:21am Subject: =?utf-8?B?5Zue5aSN77yaIOWbnuWkje+8miBXaHkgaXMgY3JlYXRpbmcgaW5kZXhlcyBm?= =?utf-8?B?YXN0ZXIgYWZ0ZXIgaW5zZXJ0aW5nIG1hc3NpdmUgZGF0YSByb3dzPw==?= List-Archive: http://lists.mysql.com/mysql/227338 Message-Id: <1336382480.2660.YahooMailNeo@web15206.mail.cnb.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-1147895278-1691782506-1336382480=:2660" ---1147895278-1691782506-1336382480=:2660 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Ok, but my opinion is that the sorting algorithms is not impact this differ= ence, two ways=C2=A0 all do B+ tree inserts.=0A=0A=0A______________________= __________=0A =E5=8F=91=E4=BB=B6=E4=BA=BA=EF=BC=9A Claudio Nanni =0A=E6=94=B6=E4=BB=B6=E4=BA=BA=EF=BC=9A Zhangzhigang =0A=E6=8A=84=E9=80=81=EF=BC=9A Johan De Meersman ; "mysql@stripped" =0A=E5=8F= =91=E9=80=81=E6=97=A5=E6=9C=9F=EF=BC=9A 2012=E5=B9=B45=E6=9C=887=E6=97=A5, = =E6=98=9F=E6=9C=9F=E4=B8=80, =E4=B8=8B=E5=8D=88 5:01=0A=E4=B8=BB=E9=A2=98: = Re: =E5=9B=9E=E5=A4=8D=EF=BC=9A Why is creating indexes faster after insert= ing massive data rows?=0A =0A=0ACreating the index in one time is one macro= -sort operation,=0Aupdating the index at every row is doing the operation o= n and on again.=0AIf you do not understand the difference I recommend you t= o read some basics about sorting algorithms,=0Avery interesting read anyway= .=0A=0AClaudio=C2=A0=0A=0A=0A2012/5/7 Zhangzhigang =0A=0Ajohan ....=0A>=0A>>Plain and simple: the indices get updated after = every insert statement,=0A>whereas if you only create the index *after* the= inserts, the index gets created in a single operation, which is a lot more= efficient..=0A>=0A>=0A>Ok, Creating the index *after* the inserts, the ind= ex gets created in a single operation.=0A>But the indexes has to be updatin= g row by row after the data rows has all been inserted. Does it work in thi= s way?=0A>So i can not find the different overhead=C2=A0 about two ways.=0A= >=0A>=0A>=0A>=0A>=0A>________________________________=0A>=C2=A0=E5=8F=91=E4= =BB=B6=E4=BA=BA=EF=BC=9A Johan De Meersman =0A>=0A>=E6= =94=B6=E4=BB=B6=E4=BA=BA=EF=BC=9A Zhangzhigang = =0A>=E6=8A=84=E9=80=81=EF=BC=9A mysql@stripped=0A>=E5=8F=91=E9=80=81= =E6=97=A5=E6=9C=9F=EF=BC=9A 2012=E5=B9=B45=E6=9C=887=E6=97=A5, =E6=98=9F=E6= =9C=9F=E4=B8=80, =E4=B8=8B=E5=8D=88 4:28=0A>=0A>=E4=B8=BB=E9=A2=98: Re: Why= is creating indexes faster after inserting massive data rows?=0A>=0A>=0A>-= ---- Original Message -----=0A>> From: "Zhangzhigang" =0A>>=0A>> Creating indexes after inserting massive data rows is faste= r than=0A>> before inserting data rows.=0A>> Please tell me why.=0A>=0A>Pla= in and simple: the indices get updated after every insert statement, wherea= s if you only create the index *after* the inserts, the index gets created = in a single operation, which is a lot more efficient.=0A>=0A>I seem to reca= ll that inside of a transaction (thus, InnoDB or so) the difference is mark= edly less; I might be wrong, though.=0A>=0A>=0A>--=0A>Bier met grenadyn=0A>= Is als mosterd by den wyn=0A>Sy die't drinkt, is eene kwezel=0A>Hy die't dr= inkt, is ras een ezel=0A=0A=0A-- =0AClaudio ---1147895278-1691782506-1336382480=:2660--