… Benchmark. In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using… data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I… [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… only one index actually used. To some extent, MySQL 5.0 and later changed this situation with the introduction of the “index merge” optimizer plan. The basic idea behind index merge is…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… indexes. But the problems with Index Merge do not stop there. It is currently rather restricted in which conditions it would do the index merge: mysql… not allow MySQL optimizer to use second index and hence index merge, what does it turn to ? It is not combined index but single index on…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…: mysql [localhost] {msandbox} (test) > select * from var55 where variable_name=’OPTIMIZER_SWITCH’ \G *************************** 1. row *************************** VARIABLE_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 1 row in set (0.00 sec) mysql [localhost…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… upcoming changes in MySQL 5.6 change the best practices for index design? Fundamentally, index design best practices will not change. MySQL 5.6… ways that MySQL can use indexes, such as: Index merge improvements. But I expect that defining the right compound index will still be superior. Index condition…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… allow to force index merge as you can do with index accesses in general. Note2 Q2/Q3 can’t use Index Merge however as it… Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth to watchout a MySQL may decide not to do Index merge…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… the index for the order by. Ranges as well as IN lists make this optimization impossible, not even speaking about index merge optimization… *************************** 1. row *************************** id: 1 select_type: SIMPLE table: utest type: index_merge possible_keys: c1,c2 key: c1,c2 key_len: 4… sec not significantly faster than sort index merge in this case. As the query time implies MySQL is not smart enough in this…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…-in, garbage-out and you risk dropping necessary indexes. To accomplish merging multiple result sets, I gathered statistics from both…indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index to Sort Data Another great benefit of BTREE index…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… which would look as following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name… not that bad with sequential IO, which is what file merge process uses. This is important illustration as in many cases…
