Bug #42094 | Optimizer chooses a wrong index when group by, order by and limit is present | ||
---|---|---|---|
Submitted: | 13 Jan 2009 23:01 | Modified: | 11 Apr 2020 2:07 |
Reporter: | Alexander Rubin | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.1 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | GROUP BY, INDEX, limit, Optimizer, order by, regression |
[13 Jan 2009 23:01]
Alexander Rubin
[13 Jan 2009 23:12]
Alexander Rubin
Uploaded ftp.mysql.com/pub/mysql/upload/t1.sql.gz
[14 Jan 2009 5:16]
Valeriy Kravchuk
Thank you for a problem report. Why do you think this is a regression? What exact previous version that worked properly you had identified?
[14 Jan 2009 14:51]
Alexander Rubin
Valeriy, here is the same query in MySQL 5.0.67: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.67 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> explain select groupid, count(wordid) as cnt from t1 where wordid=4703 group by -> groupid order by cnt desc limit 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 301926 Extra: Using where; Using index; Using temporary; Using filesort 1 row in set (0.00 sec) So it chooses the right index in MySQL 5.0 and wrong in MySQL 5.1 for the same query.
[3 Jun 2009 22:40]
Josh Orr
I just wanted to mention that I ma having this same exact problem. In my case, I order by the Primary key and in mysql 5.0 it uses the right index to restrict the result set before ordering the result. But in 5.1, it now does a full table scan (due to it using the primary key as the index to use) and filtering the result set. I think it does this so it can read the rows in the order that it needs to sort it by, but the result is it going though 7 million rows instead of 45 rows. As you can imagine, it is order's of magnitude slower. In the examples below, packageID is the primary key, and batchID has an index on it. On mysql 5.0, the execution plain are the same for both (they both use the batcHID index). Some basic examples executed with mysql 5.1: This one takes 13.54 seconds to execute: EXPLAIN SELECT packageID, Zip FROM packages WHERE batchID = 0 ORDER BY packageID; id: 1 select_type: SIMPLE table: packages type: index possible_keys: batchID key: PRIMARY key_len: 4 ref: NULL rows: 7763865 Extra: Using where This one takes .04 seconds to execute: EXPLAIN SELECT packageID, Zip FROM packages WHERE batchID = 0 ORDER BY packageID; id: 1 select_type: SIMPLE table: packages type: ref possible_keys: batchID key: batchID key_len: 8 ref: const rows: 14046 Extra:
[4 Jun 2009 23:25]
Josh Orr
Sorry, I pasted in the wrong query. The second query should read like this: EXPLAIN SELECT packageID, Zip FROM packages WHERE batchID = 0; It's the same query, except without the order by.
[12 Jul 2010 11:01]
Tomer Ratz
I ran across the same issue, on support Issue #49091. The wrong index is chosen, and a full table scan is being performed.
[9 Sep 2010 12:38]
Andrii Nikitin
bug #50394 may explain reasons for this bug, or at least related
[8 Nov 2011 13:12]
Jochen Riehm
We are frequently experiencing this problem as well. Is there any more global workaround besides adding USE INDEX to every problematic query?