August 7th, 2013, 06:27 AM
Calculate Rows Examined when using Union
I'm a novice regarding mysql optimization and I'd like to check if my understanding is correct with regards to reading the output from EXPLAIN when using union.
If I have the following query:
select name from competitors c1 where name like 'A%'
select name from competitors c2 where name like 'B%' and country_id = 2
and I use EXPLAIN to view the number of rows that mysql will examine I get the following output:
Extra: Using where; Using index
Extra: Using where
select_type: UNION RESULT
From the above I can see that 57 rows are going to be examined for table c1 and 126 for table c2, but what I want to know is whether (in the case of union) I should still calculate the product of the rows (ie. 57 x 126) to determine the final number of rows that mysql examines? If so it would equate to 7182 rows.
(It doesn't make sense to me why you'd have to multiply the rows when using union - I understand that for regular joins that don't use union that you'd need to determine the product of the rows to check how many rows are actually being examined, but is it the same when using union as in the above example? In my mind UNION is like two different resultsets that are simply appended together once individually gathered by mysql.)
Any advice would be appreciated.
August 7th, 2013, 06:29 AM
UNION simply appends rows, as you said
multiplying rows occurs in cross joins, whether written with explicit CROSS JOIN syntax or inadvertently through old-style comma joins (e.g. FROM table1, table2) without a join condition
you should use UNION ALL instead of UNION because there can be no duplicate rows coming out of your two SELECTs