Re: Dependent subquery stupidity: Is it me or the optimizer? ;-)

[Beat Vontobel]

Hi KimSeong,

> In query 3, the value of o1.i could change for each rows
> in the outer query, so need to execute the inner query once
> for every row in the outer query.

Yes, that's exactly how I see it, too. But there are only two rows in the outer query from o1. So the subquery would have to be executed twice in this example. But when it's executed, the value for o1.i from the outer query is actually a constant from the viewpoint of the subquery. So my real question is: Why does the subquery have to check all of the 10'000 rows? It should make use of the index and return a quick result from there.

Regards,
Beat

Beat Vontobel
http://www.futhark.ch/mysql