Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
If I load mysql-test/include/world{_schema,}.inc and run the following query:
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Derived table optimization has wrong estimates for ORDER BY ... LIMIT
This is a duplicate of LP bug #917990.
Re: Derived table optimization has wrong estimates for ORDER BY ... LIMIT
Fix for bug #917990 released with 5.3.4-rc.
Launchpad bug id: 916551
Re: Derived table optimization has wrong estimates for ORDER BY ... LIMIT
Note that in MySQL 5.6.4-m7 the estimate is correct - it's 10 rows for <derived2>.