pkey is the primary key for a mysql table
T. Based on the EXPLAIN output:
This query needs to scan only 1 row each for both DERIVED and PRIMARY select (as one would expect):
SELECT * FROM (SELECT * FROM T where pkey=10) t;
But this query need to do a complete linear scan for both DERIVED and PRIMARY select (this indicates that MySQL is not able to optimize the query at all):
SELECT * FROM (SELECT * FROM T) t where pkey=10;
There are at least two possible optimizations for Query #2: It can be converted to #1, or the subquery removed entirely (i.e. changing it to
SELECT * FROM T where pkey=10), and potentially others.
Is there any deeper reason that MySQL is not able to optimize the query, i.e., is it possible that optimization will change the observable behavior of the query (in which case MySQL is doing the right thing by not optimizing it) ?
PS: I am running MySQL version 5.6.13.