List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:May 7 2012 9:49am
Subject:Re: 回复: 回复: Why is creating indexes fast
er after inserting massive data rows?
View as plain text  
too nice not to share it!

http://www.youtube.com/watch?v=INHF_5RIxTE



2012/5/7 Zhangzhigang <zzgang_2008@stripped>

> Thanks, i thought about this answer in the past, and i appreciate your
> reply.
>
>
>
> ________________________________
>  发件人: Alex Schaft <alexs@stripped>
> 收件人: mysql@stripped
> 发送日期: 2012年5月7日, 星期一, 下午 4:59
> 主题: Re: 回复: Why is creating indexes faster after inserting massive data
> rows?
>
> On 2012/05/07 10:53, Zhangzhigang wrote:
> > 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.
> My simplified 2c. When inserting rows with active indexes one by one
> (insert), mysql has to
>
> 1) lock the space for the data to be added,
> 2) write the data,
> 3) lock the index,
> 4) write the index key(s),
> 5) unlock the index,
> 6)unlock the data
>
> This happens for each row
>
> When first doing all data without index, 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 unlock the index.
>
> If you make an omelet, do you fetch your eggs from the fridge one by one,
> or all at the same time? :)
>
> HTH,
> Alex
>
>
> -- MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>



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