[MDEV-2876] LP:916551 - Derived table optimization has wrong estimates for ORDER BY ... LIMIT Created: 2012-01-14  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug916551.xml    

 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.

 Comments   
Comment by Sergei Petrunia [ 2012-01-14 ]

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>.

Comment by Igor Babaev [ 2012-01-18 ]

Re: Derived table optimization has wrong estimates for ORDER BY ... LIMIT
This is a duplicate of LP bug #917990.

Comment by Elena Stepanova [ 2012-03-18 ]

Re: Derived table optimization has wrong estimates for ORDER BY ... LIMIT
Fix for bug #917990 released with 5.3.4-rc.

Comment by Rasmus Johansson (Inactive) [ 2012-03-18 ]

Launchpad bug id: 916551

Generated at Thu Feb 08 06:44:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.