From: Claudio Nanni
Date: May 7 2012 9:01am
Subject: =?GB2312?Q?Re=3A_=BB=D8=B8=B4=A3=BA_Why_is_creating_indexes_faster_after_i?=
	=?GB2312?Q?nserting_massive_data_rows=3F?=
List-Archive: http://lists.mysql.com/mysql/227337
Message-Id: <CAH7gepcZdSiFb3jqcDX4j3Wx9DqOtzrxc1VxoXVkVrihBLrDNA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=bcaec54ee62cabaa7004bf6e8279

--bcaec54ee62cabaa7004bf6e8279
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: quoted-printable

Creating the index in one time is one macro-sort operation,
updating the index at every row is doing the operation on and on again.
If you do not understand the difference I recommend you to read some basics
about sorting algorithms,
very interesting read anyway.

Claudio

2012/5/7 Zhangzhigang <zzgang_2008@stripped>

> johan ....
> >Plain and simple: the indices get updated after every insert statement,
> whereas if you only create the index *after* the inserts, the index gets
> created in a single operation, which is a lot more efficient..
>
>
> Ok, Creating the index *after* the inserts, the index gets created in a
> single operation.
> But the indexes has to be updating row by row after the data rows has all
> been inserted. Does it work in this way?
> So i can not find the different overhead  about two ways.
>
>
>
>
>
> ________________________________
>  =B7=A2=BC=FE=C8=CB=A3=BA Johan De Meersman <vegivamp@stripped>
> =CA=D5=BC=FE=C8=CB=A3=BA Zhangzhigang <zzgang_2008@stripped>
> =B3=AD=CB=CD=A3=BA mysql@stripped
> =B7=A2=CB=CD=C8=D5=C6=DA=A3=BA 2012=C4=EA5=D4=C27=C8=D5, =D0=C7=C6=DA=D2=
=BB, =CF=C2=CE=E7 4:28
> =D6=F7=CC=E2: Re: Why is creating indexes faster after inserting massive =
data rows?
>
> ----- Original Message -----
> > From: "Zhangzhigang" <zzgang_2008@stripped>
> >
> > Creating indexes after inserting massive data rows is faster than
> > before inserting data rows.
> > Please tell me why.
>
> Plain and simple: the indices get updated after every insert statement,
> whereas if you only create the index *after* the inserts, the index gets
> created in a single operation, which is a lot more efficient.
>
> I seem to recall that inside of a transaction (thus, InnoDB or so) the
> difference is markedly less; I might be wrong, though.
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



--=20
Claudio

--bcaec54ee62cabaa7004bf6e8279--