When 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 right. Numbers would be different but it should not change general point of having optimization of moving LIMIT 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 it is. I did not have the same data as I used for the other test but I created similar test case - table with separate indexes on “a” and “b” columns with cardinality of 100, having about 40.000.000 of rows
[sql]
select * from test.abc where i=5 union select * from test.abc where j=5
[\sql]
This original query was taking about 22 seconds.
As I modified it:
[sql]
select * from test.abc where i=5 union all select * from test.abc where j=5 and i!=5
[\sql]
The query time dropped to about 6 seconds which is 3.5 times faster - quite considerable improvement.
As you can notice I added “i!=5″ clause - this is what allows us to ensure we do not have duplicate rows in result set matching both conditions and so result will be same as query with “i=5 or j=5″ where clause.
I also tried this original query (which uses index merge method in MySQL 5.0):
[sql]
select * from test.abc where i=5 or j=5
[\sql]
Such query takes 4 seconds so if you do not need to trick with order by and limit using index merge is faster than UNION as it indeed should be.
So why UNION ALL is faster than UNION DISTINCT ?
The first informed guess would be - because UNION ALL does not need to use temporary table to store result set, however this is not correct - both UNION ALL and UNION distinct use temporary table for result generation. Perhaps one more thing for Optimizer Team to look into.
Interesting enough the fact UNION and UNION ALL require temporary table can only be seen in SHOW STATUS - EXPLAIN does not want to tell you this shameful fact:
[sql]
mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where j=5 and i!=5) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: abc
type: ref
possible_keys: i
key: i
key_len: 5
ref: const
rows: 348570
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: UNION
table: abc
type: ref
possible_keys: i,j
key: j
key_len: 5
ref: const
rows: 349169
Extra: Using where
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
3 rows in set (0.00 sec)
[\sql]
In fact EXPLAIN output is the same for UNION and UNION ALL (which is too bad as execution for them is obviously different).
The difference in execution speed comes from the fact UNION requires internal temporary table with index (to skip duplicate rows) while UNION ALL will create table without such index.
This also explains why difference becomes larger when on disk table is required (as in this case) - Hash indexes used by MEMORY table are very efficient and do not give so much overhead.
Entry posted by peter | No comment
Add to: | | | |


UNION vs UNION ALL Performance - Read More...