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.