… performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in….000 of rows select * from test.abc where i=5 union select * from test.abc where j=5 This original query was taking…: mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where j=5 and i!=5) \G *************************** 1…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…,combined | i1,i2 | 4,4 | NULL | 203803 | Using union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+———————————+ 1 row in set (0.00 sec) This query… | i1,i2 | 4,4 | NULL | 299364 | Using sort_union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ 1 row in set (0.00 sec) As a…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in …systems memory. I’ve benchmarked simple queries using where clause which covers multiple columns: Q1 SELECT sum…not to do Index merge (either intersection or union) but instead do full table scan or …
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… further. open_files_limit is 5000 by default now vs 1024. innodb_auto_extend_increment is now 64MB instead …var55 where variable_name=’OPTIMIZER_SWITCH’ \G *************************** 1. row *************************** VARIABLE_NAME: OPTIMIZER_SWITCH VARIABLE_VALUE: index_merge=on,index_merge_union=…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…time, the number of tmp tables created in memory vs on-disk, percentage of queries that needed full scan,…5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp… PARTITIONS*/ SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’\G This is the actual part of…
