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%’
union
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:
id: 1
select_type: PRIMARY
table: c1
type: range
possible_keys: idx_competitor_name
Key: idx_competitor_name
key_len: 66
ref:
rows: 57
Extra: Using where; Using index
id: 2
select_type: UNION
table: c2
type: ref
possible_keys: idx_competitor_name,idx_country_id
Key: idx_country_id
key_len: 4
ref: const
rows: 126
Extra: Using where
select_type: UNION RESULT
table: <union1,2>
type: ALL
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.