Re: MYSQL optimizer: Handling Subqueires

[Rick James]

Others spend more time computing the optimal query plan, then freeze for all values. This is sub-optimal in situations where different plans work better for different values. I have seen as many as 6 different plans for a single query (varying the constants and the phase of the moon).

MySQL takes the approach that a fast optimizer that uses the constants in question is better than a thorough optimizer. I am almost always happy with MySQL's choice.

I believe MySQL's optimizer also notices what happens to be cached at the moment, picking an unobvious index.

The time taken for the subquery is not wasted since the result is used in the execution. And that subquery had to be performed anyway. The outer query may be better optimized because of having the result of the inner query.