From: Zhangzhigang
Date: May 7 2012 9:30am
Subject: =?utf-8?B?5Zue5aSN77yaIOWbnuWkje+8miBXaHkgaXMgY3JlYXRpbmcgaW5kZXhlcyBm?=
 =?utf-8?B?YXN0ZXIgYWZ0ZXIgaW5zZXJ0aW5nIG1hc3NpdmUgZGF0YSByb3dzPw==?=
List-Archive: http://lists.mysql.com/mysql/227339
Message-Id: <1336383005.85617.YahooMailNeo@web15206.mail.cnb.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="-1147895278-574959217-1336383005=:85617"

---1147895278-574959217-1336383005=:85617
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Thanks, i thought about this answer in the past, and i appreciate your repl=
y.=0A=0A=0A=0A________________________________=0A =E5=8F=91=E4=BB=B6=E4=BA=
=BA=EF=BC=9A Alex Schaft <alexs@stripped>=0A=E6=94=B6=E4=BB=B6=
=E4=BA=BA=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:59=0A=E4=B8=BB=E9=A2=98: Re: =E5=9B=9E=E5=A4=8D=
=EF=BC=9A Why is creating indexes faster after inserting massive data rows?=
=0A =0AOn 2012/05/07 10:53, Zhangzhigang wrote:=0A> johan ....=0A>> Plain a=
nd simple: the indices get updated after every insert statement,=0A> 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> =0A> Ok, Cr=
eating the index *after* the inserts, the index gets created in a single op=
eration.=0A> But the indexes has to be updating row by row after the data r=
ows has all been inserted. Does it work in this way?=0A> So i can not find =
the different overhead=C2=A0 about two ways.=0AMy simplified 2c. When inser=
ting rows with active indexes one by one (insert), mysql has to=0A=0A1) loc=
k the space for the data to be added,=0A2) write the data,=0A3) lock the in=
dex,=0A4) write the index key(s),=0A5) unlock the index,=0A6)unlock the dat=
a=0A=0AThis happens for each row=0A=0AWhen first doing all data without ind=
ex, only 1, 2, and 6 happen. When you then create an index, it can lock the=
 index, read all the data and write all index keys in one go and then unloc=
k the index.=0A=0AIf you make an omelet, do you fetch your eggs from the fr=
idge one by one, or all at the same time? :)=0A=0AHTH,=0AAlex=0A=0A=0A-- My=
SQL General Mailing List=0AFor list archives: http://lists.mysql.com/mysql=
=0ATo unsubscribe:=C2=A0 =C2=A0 http://lists.mysql.com/mysql
---1147895278-574959217-1336383005=:85617--