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:
None 
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 21:54] Ruslan Zakirov
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.
[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.