List:General Discussion« Previous MessageNext Message »
From:Zhangzhigang Date:May 7 2012 9:21am
Subject:回复: 回复: Why is creating indexes f
aster after inserting massive data rows?
View as plain text  
Ok, but my opinion is that the sorting algorithms is not impact this difference, two
ways  all do B+ tree inserts.


________________________________
 发件人: Claudio Nanni <claudio.nanni@stripped>
收件人: Zhangzhigang <zzgang_2008@stripped> 
抄送: Johan De Meersman <vegivamp@stripped>; "mysql@stripped"
<mysql@stripped> 
发送日期: 2012年5月7日, 星期一, 下午 5:01
主题: Re: 回复: Why is creating indexes faster after inserting massive data rows?
 

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.
>
>
>
>
>
>________________________________
> 发件人: Johan De Meersman <vegivamp@stripped>
>
>收件人: Zhangzhigang <zzgang_2008@stripped>
>抄送: mysql@stripped
>发送日期: 2012年5月7日, 星期一, 下午 4:28
>
>主题: 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


-- 
Claudio
Thread
Why is creating indexes faster after inserting massive data rows?Zhangzhigang7 May
  • Re: Why is creating indexes faster after inserting massive data rows?Ananda Kumar7 May
    • 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang7 May
  • Re: Why is creating indexes faster after inserting massive data rows?Johan De Meersman7 May
    • 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang7 May
      • Re: 回复: Why is creating indexes faster after inserting massive data rows?Alex Schaft7 May
        • 回复: 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang7 May
          • Re: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Claudio Nanni7 May
            • too nice not to share it!hsv2 Oct
          • Re: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Mihamina Rakotomandimby30 May
      • Re: 回复: Why is creating indexes faster after inserting massive data rows?Claudio Nanni7 May
        • 回复: 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang7 May
      • Re: 回复: Why is creating indexes faster after inserting massive data rows?Johan De Meersman7 May
        • RE: 回复: Why is creating indexes faster after inserting massive data rows?Rick James7 May
        • Re: 回复: Why is creating indexes faster after inserting massive data rows?Karen Abgarian7 May
          • 回复: 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang8 May
            • Re: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Karen Abgarian8 May
              • 回复: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang8 May
                • Re: 回复: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Johan De Meersman8 May
                  • 回复: 回复: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang8 May
                  • Re: 回复: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Karen Abgarian8 May
                    • 回复: 回复: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang9 May
              • RE: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Rick James10 May
            • Re: 回复: 回复: Why is creating indexes faster after inserting massive data rows?Johan De Meersman8 May
        • Re: 回复: Why is creating indexes faster after inserting massive data rows?Karen Abgarian7 May
    • RE: Why is creating indexes faster after inserting massive datarows?Rick James7 May
      • 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang9 May
        • RE: 回复: Why is creating indexes faster after inserting massive data rows?Rick James9 May
          • Re: 回复: Why is creating indexes faster after inserting massive data rows?Claudio Nanni9 May
          • 回复: 回复: Why is creating indexes faster after inserting massive data rows?Zhangzhigang10 May