#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Question Need Help with reading Explain


    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    the latter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Smile Re: Need Help with reading Explain


    Originally Posted by r937
    the latter
    Thank you. I have a better understanding of Mysql query optimization as a result of your replies to my various posts

IMN logo majestic logo threadwatch logo seochat tools logo