MySQL query sequence

In which sequence are queries and sub-queries executed by the SQL engine?

the answers was

1. primary query -> sub query -> sub sub query and so on
2. sub sub query -> sub query -> prime query
3. the whole query is interpreted at one time
4. There is no fixed sequence of interpretation, the query parser takes a decision on fly

I choosed the last answer (just supposing that it is most reliable w.r.t. others). Now the curiosity:

where can i read about this and briefly what is the mechanism under all of that?

ans:

SQL is declarative: you tell the query optimiser what you want and it works out the best (subject to time/"cost" etc) way of doing it. This may vary for outwardly identical queries and tables depending on statistics, data distribution, row counts, parallelism and god knows what else.

This means there is no fixed order. But it's not quite "on the fly"

Even with identical servers, schema, queries, and data I've seen execution plans differ

In which sequence are queries and sub-queries executed by the SQL engine?

the answers was

1. primary query -> sub query -> sub sub query and so on
2. sub sub query -> sub query -> prime query
3. the whole query is interpreted at one time
4. There is no fixed sequence of interpretation, the query parser takes a decision on fly

I choosed the last answer (just supposing that it is most reliable w.r.t. others). Now the curiosity:

where can i read about this and briefly what is the mechanism under all of that?

answer 4 is correct. There are a few considerations:

type of subquery - is it corrrelated, or not. Consider:

SELECT *
FROM   t1
WHERE  id IN (
             SELECT id
             FROM   t2
            )

Here, the subquery is not correlated to the outer query. If the number of values in t2.id is small in comparison to t1.id, it is probably most efficient to first execute the subquery, and keep the result in memory, and then scan t1 or an index on t1.id, matching against the cached values.

But if the query is:

SELECT *
FROM   t1
WHERE  id IN (
             SELECT id
             FROM   t2
             WHERE  t2.type = t1.type
            )

here the subquery is correlated - there is no way to compute the subquery unless t1.type is known. Since the value for t1.type may vary for each row of the outer query, this subquery could be executed once for each row of the outer query.

Then again, the RDBMS may be really smart and realize there are only a few possible values for t2.type. In that case, it may still use the approach used for the uncorrelated subquery if it can guess that the cost of executing the subquery once will be cheaper that doing it for each row.

from：  http://stackoverflow.com/questions/2263186/in-which-sequence-are-queries-and-sub-queries-executed-by-the-sql-engine