I need help in determining the number of rows examined when viewing the output from Explain with regards to the following query:
select y.id, y.name, y.age
from
(select x.id, x.name, x.age
from
(select u.id, u.name, u.age
from users u
where u.gender = ‘female’
union all
select u.id, u.name, u.age
from users u
where u.gender = ‘male’) as x
group by x.age) as y
where y.age > 20
The number of rows from the queries using union equate to 500 rows. The number of rows read for the outer query (“x”) is 60 and the rows for the outermost query (“y”) is 20. What is the final number of rows read, is it: 500 x 60 x 20 (600 000 rows) or 500 + 60 + 20 (580 rows)?
Your help in this regard would be appreciated.