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

[Beat Vontobel]

Hi Jay, thanks for your efforts in forwarding to all the right people. Maybe somebody knows about a similar bug report/feature request? Then I could subscribe there. The nice thing about the bugs system is, that it allows me to easily keep track of all my open issues. Without a report I'll have to check all my issues again on every new build (as there is usually a dependent project waiting or an ugly workaround to be removed at our site for every one of them).

There was also some additional communication on this via mail with Markus Popp, so I think I'll post the important part here just for completeness (hope it's okay with Markus):

Markus:

> The execution times between query 2 and 3 were 0.0029 sec vs. 0.0288 sec
> when I tested them, so the factor is about 10. So I'm not quite sure whether
> the third query really checked all 10,000 records - maybe it's also an error
> in the output.

My response:

> To really see how painfully slow the query is, execute it with 1'000'000
> instead of 10'000 rows:
>
> Constant in outer query (query two):
>
> +----+----+
> | o1 | o2 |
> +----+----+
> | 50 | 51 |
> +----+----+
> 1 row in set (0.00 sec)
>
> BETWEEN in outer query (query three):
>
> +----+----+
> | o1 | o2 |
> +----+----+
> | 50 | 51 |
> | 51 | 52 |
> +----+----+
> 2 rows in set (12.08 sec)

Cheers,
Beat

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