…. row *************************** id: 1 select_type: SIMPLE table: users type: index_merge possible_keys: PRIMARY,parent_id,status,user_type key: user… issue, and they are all straightforward to implement. The bad (sort of) news is that each situation is going to be… simply adjust the optimizer_switch configuration setting and disable index_merge_intersection. It’s a dynamically-adjustable setting and can be…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
….commanual myisam_recover_optionsblogpercona.commanual myisam_repair_threadsblogpercona.commanual myisam_sort_buffer_sizeblogpercona.commanual myisam_stats_methodblogpercona.commanual myisam_use_mmapblogpercona…_query_log_fileblogpercona.commanual socketblogpercona.commanual sort_buffer_sizeblogpercona.commanual Sort_merge_passesblogpercona.commanual Sort_rangeblogpercona.commanual Sort_rowsblogpercona.commanual Sort_scanblogpercona.commanual sporadic_binlog_dump…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… to memory any more. Creating Index by sort is great Creating index by sort can provide over 20x performance improvement to the… clearly seen from the graphs but with index built by sort enabled the performance of ALTER TABLE with 128MB was better… not that bad with sequential IO, which is what file merge process uses. This is important illustration as in many cases…
Comment: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… you to log queries which trigger counters of things like sort_merge_pass, full joins and tmp tables on disk so you… them. I do have 0 Select_full_join and 0 Sort_merge_passes though finally. What is most confidence inspiring is thinking…
Comment: What to tune in MySQL Server after installation
December 28, 2006 By 118 Comments
… at sort_merge_passes status variable. If this variable is increased sort_buffer was not enough to accommodate all data to perform the sort and file based sort was used. Note you do not always have to increase global sort_buffer value. In many cases setting per connection variable before…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…’ ‘Slow_launch_threads’, ’0′ ‘Slow_queries’, ’27405′ ‘Sort_merge_passes’, ’376′ ‘Sort_range’, ’56479689′ ‘Sort_rows’, ’1344092495′ ‘Sort_scan’, ’935242′ ‘Table_locks_immediate’, ’895966639′ ‘Table… key_buffer_size=32M # Only for MyISAM which isnt used. sort_buffer_size=2M query_cache_size=10M # Might be increased…
Comment: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… proportion of more costly queries and/or low query rates. sort_buffer_size is much improved over the past 2M but… speed for OLTP that tends to involve lots of small sorts We considered going smaller but went with 256 as a… (no smaller) and 32k and look at SHOW GLOBAL STATUS Sort_merge_passes as an initial tool to see whether there’s…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… indexes are created equal. Some indexes may be placed in sorted way or pages placed in random places – this may affect… example retrieving index values first and then accessing rows in sorted order can be a lot of help for big scans… on a way) – MySQL can’t do hash join or sort merge join – it only can do nested loops method which requires…
Comment: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
September 18, 2012 By 4 Comments
Sheeri, Creating index by sort had multiple bugs over the years and we want move … have the case when data will fit in memory while sort-merge file will not because it uses fixed length which can…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… large data sets are very expensive. If MySQL supported sort-merge or hash joins, you’d have other possibilities, but it… often justified by a large reduction in table size. I sort of stumbled upon this idea one day. I have no…
