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

[Björn Steinbrink]

Hi Beat,

after spending some time with 'smart' approaches to your problem, I realized that "MIN(i)" is just a smart way of "i ORDER BY i ASC LIMIT 1". And guess what, the not-so-smart approach actually does the trick ;)

mysql> CALL fill_test(1000000);
Query OK, 1 row affected (34.72 sec)

mysql> SELECT o1.i AS o1, o2.i AS o2 FROM test AS o1 INNER JOIN test AS o2 ON (
SELECT MIN(i) FROM test WHERE i > o1.i ) = o2.i WHERE o1.i BETWEEN 50 AND 51;
+----+----+
| o1 | o2 |
+----+----+
| 50 | 51 |
| 51 | 52 |
+----+----+
2 rows in set (2.46 sec)

mysql> SELECT o1.i AS o1, o2.i AS o2 FROM test AS o1 INNER JOIN test AS o2 ON (
SELECT i FROM test WHERE i > o1.i ORDER BY i ASC LIMIT 1) = o2.i WHERE o1.i BETW
EEN 50 AND 51;
+----+----+
| o1 | o2 |
+----+----+
| 50 | 51 |
| 51 | 52 |
+----+----+
2 rows in set (0.00 sec)

HTH