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

[Beat Vontobel]

> No need to file a bug report right now.

Sorry, still had to add this to the bugs system: http://bugs.mysql.com/bug.php?id=18465

I need to be able to track this and to add an associated issue to the MySQL Network support. One of our projects heavily depends on it.

Any ideas on a clever workaround that's optimized better by MySQL, anybody?

Everything I checked was similar or even worse. And I need to JOIN rows to their successors on a table with approximately 78 million rows. The query is actually supposed to return immediately (as it would if this would be optimized properly) - I can't wait for minutes or even hours for the result.

(The last solution would just be to move the JOIN out from the query to the application and build the result set there... it's not really efficient if I have to send the "subquery"/"join part" for every row over the network to the database server, this creates an immense overhead even if prepared statements are used... then I'd probably better just read in a whole bunch of rows sorted by that key from the database and always join two consecutive rows together, completely bypassing MySQL.)

Regards,
Beat

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