… second SQL query when using index merge executes in 3.8 secs. I dont understand why MySQL uses index merge after a couple of mins when previously it was using a single index. I would greatly appreciate…_ix | PRIMARY | 4 | const | 1 | Using filesort | | 1 | SIMPLE | LinksTbl2 | index_merge | tstamp,userid,converted_from_id,item_desc,treeid_ix,prjid…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 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… optimizer to use second index and hence index merge, what does it turn to ? It is not combined index but single index on another column… index in this case. In case you’re using OR between columns – single column indexes are required for index merge to work and combined indexes…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when… 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… benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth…
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…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… and prefix range matches. So if you have index on (A,B) This index can be used to lookup rows for WHERE… 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: Merge Tables Gotcha
May 9, 2007 By 4 Comments
… obviously two rows with value 1 in merge table however select querying by primary index returns only one. Following EXPLAIN shows why… column is indexed as a PRIMARY KEY in the underlying MyISAM tables, but not in the MERGE table. There it is indexed but not as a PRIMARY KEY because a MERGE table cannot enforce uniqueness…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… index creation results in much less fragmented indexes because records are inserted in the correct order into sequentially allocated pages after merge-sorting. So besides optimizing DDL directly, expand_fast_index_creation may also optimize index access for subsequent DML…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 13 Comments
… I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL…” where clause. I also tried this original query (which uses index merge method in MySQL 5.0): select * from test.abc where… not need to trick with order by and limit using index merge is faster than UNION as it indeed should be. So…
Comment: Using UNION to implement loose index scan in MySQL
August 11, 2006 By 15 Comments
… one of indexes only or to intersect row pointers from both indexes. If you would need OR in this case Index merge would apply…_keys | key | key_len | ref | rows | Extra | +—-+————-+——–+————-+—————+———+———+——+——-+—————————————-+ | 1 | SIMPLE | people | index_merge | age,zip | age,zip | 1,3 | NULL | 38821 | Using sort…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… can use either indexes built on multiple columns or intersection of multiple accesses by indexes. When we talk about index_merge_intersection, we should understand that mysql finds one set using an index, another set…
