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

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Question Help Needed to Calculate Rows Examined


    I am still in the process of learning how to read the output from EXPLAIN. I would like to be able to determine the total number of rows mysql will examine for the following query that uses a subquery. The output from explain for this query is as follows:

    id----select_type----------table------------------rows
    1-----PRIMARY----------<derived2>----------12226
    1-----PRIMARY----------p------------------------1
    1-----PRIMARY----------p2c---------------------2
    2-----DERIVED----------t ------------------------25951
    2-----DERIVED----------b------------------------1
    2-----DERIVED----------a------------------------1

    The query is in the following form:

    select col1, col2
    from table p, table p2c,
    (select col1, col2
    from table t, b, a
    where ....)
    where ....

    My understanding is that the inner query will be worked out and in this case mysql will examine 25951 rows and will then store the results in a temporary table (from the above it can be seen that the number of rows in this table equate to 12226). What is important to me is how to work out the final number of rows examined, to me it makes sense to work out the inner query first (ie. 25951 x 1 x 1) and then work out the outer query (ie. 12226 x 1 x 2) and add the two figures, ie. (25951 + 24452 which equates to 50403 rows). I'm not sure if this is incorrect or whether I must simply multiply all the rows (ie. 12226 x 1 x 2 x 25951 x 1 x 1 equating to 634553852 rows!) which does not seem correct to me.

    Which is correct: 50403 rows or 634553852 rows?

    Your response would be appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    the former
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo