List:General Discussion« Previous MessageNext Message »
From:Zhangzhigang Date:May 9 2012 4:13am
Subject:回复: Why is creating indexes faster afte
r inserting massive data rows?
View as plain text  
James...
>* By doing all the indexes after building the table (or at least all the non-UNIQUE
indexes), "sort merge" can be used.  This technique had been highly optimized over
the past half-century, and is more efficient.


I have a question about "sort merge":

Why does it do the all "sort merge"? 


In my opinion, it just maintains the B tree and inserts one key into a B tree node which
has fewer sorted keys, so it is good performance.

If it only does the "sort merge", the B tree data structure have to been
createdseparately. it wastes some performance.

Does it?



________________________________
 发件人: Rick James <rjames@stripped>
收件人: Johan De Meersman <vegivamp@stripped>; Zhangzhigang
<zzgang_2008@stripped> 
抄送: "mysql@stripped" <mysql@stripped> 
发送日期: 2012年5月8日, 星期二, 上午 12:35
主题: RE: Why is creating indexes faster after inserting massive data rows?
 
* Batch INSERTs run faster than one-row-at-a-time, but this is unrelated to INDEX
updating speed.
* The cache size is quite important to dealing with indexing during INSERT; see
http://mysql.rjweb.org/doc.php/memory 
* Note that mysqldump sets up for an efficient creation of indexes after loading the
data.  This is not practical (or necessarily efficient) when incremental INSERTing
into a table.

As for the original question...
* Updating the index(es) for one row often involves random BTree traversals.  When
the index(es) are too big to be cached, this can involve disk hit(s) for each row
inserted.
* By doing all the indexes after building the table (or at least all the non-UNIQUE
indexes), "sort merge" can be used.  This technique had been highly optimized over
the past half-century, and is more efficient.


> -----Original Message-----
> From: Johan De Meersman [mailto:vegivamp@stripped]
> Sent: Monday, May 07, 2012 1:29 AM
> To: Zhangzhigang
> Cc: mysql@stripped
> Subject: 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
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql 
> To unsubscribe:    http://lists.mysql.com/mysql 
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