| 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