… 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… | oslogs_jan02_db.LinksTbl2.userid | 1 | | | 1 | SIMPLE | FileTbl | eq_ref | PRIMARY,folder_desc_ix | PRIMARY | 4 | oslogs_jan02_db.LinksTbl2…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ | 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i2,i1 | 4,4 | NULL… | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ | 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL…
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…: SIMPLE table: utest type: index_merge possible_keys: c1,c2 key: c1,c2 key_len: 4,4 ref: NULL rows: 162380 Extra: Using… completes in 1.05 sec not significantly faster than sort index merge in this case. As the query time implies MySQL is…
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…: type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 3 rows in set (0.00…
Post: Merge Tables Gotcha
May 9, 2007 By 4 Comments
… things happens in JOIN queries when it would use “eq_ref” lookup type which means it will only do one lookup… 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…
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… | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——–+————-+—————+———+———+——+——-+—————————————-+ | 1 | SIMPLE | people | index_merge | age,zip | age,zip | 1,3 | NULL | 38821…
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… don’t have such compound index then mysql most probably use index on “available” column only using ref access and after that filter…
Comment: Multi Column indexes vs Index Merge
June 1, 2010 By 30 Comments
… | type | possible_keys | KEY | key_len | ref | rows | Extra | 4. +—-+————-+———+——+—————-+———-+———+————-+——+——-+ 5. | 1 | SIMPLE | idxtest | ref | i1,i2,combined | combined | 8 | const,const… | type | possible_keys | KEY | key_len | ref | rows | Extra | 4. +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 5. | 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i2,i1 | 4,4…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…index | +—-+————-+————+——-+—————+——+———+——+———+————-+ 2 rows in set (1.76 sec) Now to the actual workaround. MySQL won’t merge…| ref | rows | Extra | …
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
…counts. MySQL has two ways of handling the VIEWS – query merge, in which case VIEW is simply expanded as a … DERIVED table: comments type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 1792695 Extra: Using index *************************** 4. row *************************** id…
