Derived table optimization has wrong estimates for ORDER BY ... LIMIT
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Igor Babaev |
Bug Description
If I load mysql-test/
MariaDB [world]> explain select * from (select * from Country order by Population ASC limit 10) as small_country ;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 239 | |
| 2 | DERIVED | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using filesort |
+----+-
2 rows in set (0.00 sec)
I get 239 as the number of rows which will be scanned in table <derived2>.
This estimate is wrong, it should be easy to figure that "LIMIT 10" caps table
size to 10 rows.
Changed in maria: | |
status: | Confirmed → Fix Committed |
Note that in MySQL 5.6.4-m7 the estimate is correct - it's 10 rows for <derived2>.