SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need Help 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 a query that uses a subquery in the FROM clause. 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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the former
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •