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.