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 to Calculate Rows Examined

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

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

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Why are you concerned about this? Seems an odd thing to be focussed on.

    Is the query slow? You may find there is a better way of writing it, or that the indexes aren't right.
     
    sarahk, Aug 17, 2013 IP
  3. Normandy

    Normandy Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    Hi Sarah,

    The query is fast, but my hosting provider performed an Explain on the query and insisted that the Explain Output is unacceptable for the query when used within a shared hosting environment. I have since learnt that the hosting provider's technical support team incorrectly calculated the rows_examined.

    The correct answer to my question is: 50403 rows.

    Thanks for taking the time to respond.
     
    Normandy, Sep 17, 2013 IP