… InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer… used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt… metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer…
Post: High-load problems? Investigate them with 'pt-query-digest'
April 23, 2013 By 2 Comments
…, but at least you get a nice ranking of queries sorted by the number of times they appear in processlist. In…. Rewriting that query (actually the customer removed it altogether, by merging it with another query that ended up having a much…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… sec) As you can see MySQL 5.1.21 uses sort_merge to access the rows but it can’t use it… you well could be retrieving sorted streams by the indexes and doing merge sort to get resulted rows in sorted order. Once this is… in completes in 1.05 sec not significantly faster than sort index merge in this case. As the query time implies MySQL…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… sort_buffer allowed when a lot of sort merge passes are required for sort completion: mysql> show status like “sort%”; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | Sort_merge_passes | 321 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
…” | bc` echo “$i $MERGE $TIME” done which in addition to output the total time, output the number of Sort_merge_passes, which will… the number of sort merge passes. For the small values of the sort buffer size, below 440KB, there are many sort merge passes and the time for the queries hover around 18s. Above 440K, as the sort merge passes drops to…
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
… almost any such index build operation has to use excessive sort merge passes significantly slowing down index built process. Mark Callaghan and… early 2011 adding innodb_merge_sort_block_size variable and I was thinking this small patch will be merged to MySQL 5.5… rebuild is so close in performance to building index by sort with small buffer – this comes from building index on long…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… small sorts for which allocation of 2M for sort buffer was very expensive. It can negatively impact some large sorts though shifting to sort merge…_NAME: OPTIMIZER_SWITCH VARIABLE_VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on…
Comment: How fast can you sort data with MySQL ?
August 6, 2009 By 26 Comments
…。这å†æ¬¡æŽ’åºå°±ä¼šå¢žåŠ Sort_merge_passes。实际上，MySQL 会用å¦ä…的结果，所以通常会看到 Sort_merge_passes å¢žåŠ çš„æ•°å€¼æ˜¯å»ºä¸´æ…½ä¼šæ¯”è¾ƒæ…¢ï¼Œå¢žåŠ Sort_buffer_size 会å‡å°‘ Sort_merge_passes å’Œ åˆ›å»ºä¸´æ—¶æ–‡ä»¶çš„æ¬¡æ•°ã€‚ä½†ç›²ç›®çš„å¢žåŠ Sort_buffer…
Comment: How fast can you sort data with MySQL ?
March 25, 2009 By 26 Comments
…。这å†æ¬¡æŽ’åºå°±ä¼šå¢žåŠ Sort_merge_passes。实际上，MySQL 会用å¦ä…的结果，所以通常会看到 Sort_merge_passes å¢žåŠ çš„æ•°å€¼æ˜¯å»ºä¸´æ…½ä¼šæ¯”è¾ƒæ…¢ï¼Œå¢žåŠ Sort_buffer_size 会å‡å°‘ Sort_merge_passes å’Œ åˆ›å»ºä¸´æ—¶æ–‡ä»¶çš„æ¬¡æ•°ã€‚ä½†ç›²ç›®çš„å¢žåŠ Sort_buffer…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… | key | key_len | ref | rows | Extra | +—-+————-+———+————-+—————+——-+———+——+——+————————————-+ | 1 | SIMPLE | idxtest | index_merge | i1,i2 | i1,i2 | 4,4 | NULL | 1032 | Using intersect… | Extra | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ | 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 299364 | Using sort_union(i1,i2); Using where…
