| Bug #20302 | Union all is not 'streaming' | ||
|---|---|---|---|
| Submitted: | 6 Jun 2006 18:15 | Modified: | 31 Mar 2009 18:19 |
| Reporter: | Andre Timmer | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 5.1.12, 5.0.23-BK, 5.0.18 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Jun 2006 10:04]
Valeriy Kravchuk
Thank you for a reasonable feature request. Even in 5.0.23 we have: mysql> EXPLAIN EXTENDED select * from ( select * from t1 union all select * from t1) aa limit 5; +----+--------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 12 | | | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 6 | | | 3 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 6 | | | | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+------+---------------+------+---------+------+------+-------+ 4 rows in set, 1 warning (0.00 sec) So, it looks like derived table is built first, completely. It would be nice to get results by scanning first table until LIMIT is reached, and scan second one only if it is needed. In this (unusual) case (LIMIT without ORDER BY).
[17 Aug 2007 22:56]
Igor Babaev
- This problem will be resolved when WL#3485 is implemented (not started yet). By the above reason I move the bug to 'To be fixed later' and mark it as a 'Feature request'. Product management will decide in what version a fix for this problem appears.
[4 Nov 2008 18:41]
Valeriy Kravchuk
WL#3485 should be implemented in 6.0.x. Is this problem fixed there already?
Description: Select in 'How to repeat' should gives rows back immediately. It doesn't, it looks like virtual table aa is build first.' When the word 'union' was used this would be correct. For 'union all' this is not, or at least not necessairy. How to repeat: select * from ( select * from tab1 union all select * from tab1 ) aa limit 10; No testdata supplied, you can use some hugh table you probably have for this. Suggested fix: In case of 'union all' the results of the virtual table should produce rows immediately.