… performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my… inside of union clause being cool thing. But So is UNION ALL indeed faster than UNION DISTINCT (the UNION is shortcut for UNION DISTINCT) ? Indeed… limit using index merge is faster than UNION as it indeed should be. So why UNION ALL is faster than UNION DISTINCT ? The first…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… | 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 203803 | Using union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+———————————+ 1 row… | 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 299364 | Using sort_union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ 1…
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… benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It… watchout a MySQL may decide not to do Index merge (either intersection or union) but instead do full table scan or access…
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
October 5, 2007 By 10 Comments
[...] I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my benchmarks, and he was [...]
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… OPTIMIZER_SWITCH | index_merge=on,index_merge_union=on,inde | index_merge=on,index_merge_union=on,inde | +—————————————————+——————————————+——————————————+ … is 5000 by default now vs 1024. innodb_auto_extend_…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 # InnoDB_IO_r_ops: 984 InnoDB_IO_… time, the number of tmp tables created in memory vs on-disk, percentage of queries that needed full scan, …2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_…
