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 .
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.062 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.