1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Need Help with reading Explain

Discussion in 'MySQL' started by Normandy, Aug 10, 2013.

0
  1. #1
    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.
     
    Normandy, Aug 10, 2013 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    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):
     
    sarahk, Aug 11, 2013 IP
  3. Normandy

    Normandy Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    Thank you Sarah :)
     
    Normandy, Aug 12, 2013 IP