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.
You are trying to find out how many guys and girls there are for each age? I created a "fiddle" that shows another way of doing it - and getting the row count will be lots cleaner, or the total rows "examined" will just be the total of the male and female counts. http://sqlfiddle.com/#!2/5e4b6/3 select gender , age , sum(case gender when 'female' then 1 else 0 end) as female , sum(case gender when 'male' then 1 else 0 end) as male from users where age >= 20 group by gender, age order by age Code (markup):