| Bug #37680 | optimizer uses huge index scan for order by when ref access is possible | ||
|---|---|---|---|
| Submitted: | 26 Jun 2008 21:54 | Modified: | 10 Jul 2008 8:09 |
| Reporter: | Ruslan Zakirov | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 5.1.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | index scan, order by, ref | ||
[26 Jun 2008 22:03]
Ruslan Zakirov
Looked closer into mysql's bug tracker. May be bug is related to http://bugs.mysql.com/bug.php?id=36259.
[27 Jun 2008 4:31]
Valeriy Kravchuk
Thank you for a bug report. Looks like a duplicate of bug #36259, indeed.
[27 Jun 2008 11:12]
Ruslan Zakirov
One of users upgraded to mysql 5.1.25 and it has no difference.
[27 Jun 2008 16:54]
Valeriy Kravchuk
OK, then please send the results of SHOW TABLE STATUS for that Attachments table.
[10 Jul 2008 8:09]
Ruslan Zakirov
sorry, but customer already downgraded to older version.
[24 Aug 2008 7:59]
Alan Snelson
I also noticed this issue after an upgrade to MySQL 5.1.26-rc on FreeBSD 7. Interestingly enough if you change the engine on the Attachments table to MyISAM and explain the same query again the correct key (Attachments3) is chosen!.
[24 Aug 2008 8:12]
Alan Snelson
Please find table status for Attachments
*************************** 2. row ***************************
Name: Attachments
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 820884
Avg_row_length: 3588
Data_length: 2945449984
Max_data_length: 0
Index_length: 14712832
Data_free: 7168
Auto_increment: 458426
Create_time: 2008-08-24 09:11:50
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
[30 Oct 2009 0:49]
James Day
This is a duplicate of bug #45828 that is included in 5.1.37, 5.4.2 .
[30 Oct 2009 0:50]
James Day
This is a duplicate of bug #45828 that is fixed in 5.1.37, 5.4.2 .
Description: Several users of the Request Tracker software made upgrade to mysql 5.1 from 4.x and reported similar regressions. mysql> explain SELECT main.* FROM Attachments main WHERE (main.Parent = '308267') AND (main.ContentType = 'text/plain') ORDER BY main.id ASC; +----+-------------+-------+-------+----------------------------------+---------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------------------------+---------+---------+------+---------+-------------+ | 1 | SIMPLE | main | index | Attachments3,Attachments4,Parent | PRIMARY | 4 | NULL | 1321158 | Using where | +----+-------------+-------+-------+----------------------------------+---------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> explain SELECT main.* FROM Attachments main FORCE INDEX(Attachments3) WHERE (main.Parent = '308267') AND (main.ContentType = 'text/plain') ORDER BY main.id ASC; +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | main | ref | Attachments3 | Attachments3 | 4 | const | 1 | Using where; Using filesort | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec) Schema: CREATE TABLE `Attachments` ( `id` int(11) NOT NULL auto_increment, `TransactionId` int(11) NOT NULL, `Parent` int(11) NOT NULL default '0', `MessageId` varchar(160) character set ascii default NULL, `Subject` varchar(255) default NULL, `Filename` varchar(255) default NULL, `ContentType` varchar(80) character set ascii default NULL, `ContentEncoding` varchar(80) character set ascii default NULL, `Content` longblob, `Headers` longtext, `Creator` int(11) NOT NULL default '0', `Created` datetime default NULL, PRIMARY KEY (`id`), KEY `Attachments2` (`TransactionId`), KEY `Attachments3` (`Parent`,`TransactionId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | mysql> SHOW KEYS FROM Attachments\G *************************** 1. row *************************** Table: Attachments Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: Attachments Non_unique: 1 Key_name: Attachments2 Seq_in_index: 1 Column_name: TransactionId Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: Attachments Non_unique: 1 Key_name: Attachments3 Seq_in_index: 1 Column_name: Parent Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 4. row *************************** Table: Attachments Non_unique: 1 Key_name: Attachments3 Seq_in_index: 2 Column_name: TransactionId Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 4 rows in set (0.00 sec) This is default set of indexes, some people indexed Parent field without much luck. References to original reports: http://rt3.fsck.com//Ticket/Display.html?id=9857 loging: uest/guest http://lists.bestpractical.com/pipermail/rt-users/2008-May/052134.html http://request-tracker.ru/node/64 - report in russian What else can I provide to help you debug this? How to repeat: Not sure. May be big innodb table with a blob/text column and select with possible ref access and order by id. I have no mysql 5.1 available around to try this.