Bug #45095 order by not using index
Submitted: 26 May 2009 11:18 Modified: 29 Nov 2009 0:05
Reporter: Bogdan Kecman Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.32, 4.1, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[26 May 2009 11:18] Bogdan Kecman
Description:
The: http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html
states that:

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

will use index for order by.... and according to explain - it does not

How to repeat:
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `orderby` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `orderby` (`orderby`)
) ENGINE=MyISAM AUTO_INCREMENT=6167432 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 6167431
 Avg_row_length: 17
    Data_length: 104846327
Max_data_length: 4785074604081151
   Index_length: 201570304
      Data_free: 0
 Auto_increment: 6167432
    Create_time: 2009-05-26 12:54:55
    Update_time: 2009-05-26 12:58:48
     Check_time: 2009-05-26 12:59:42
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> analyze table t1;
+---------+---------+----------+-----------------------------+
| Table   | Op      | Msg_type | Msg_text                    |
+---------+---------+----------+-----------------------------+
| test.t1 | analyze | status   | Table is already up to date | 
+---------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> show index from t1\G
*************************** 1. row ***************************
       Table: t1
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 6167431
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: t1
  Non_unique: 1
    Key_name: orderby
Seq_in_index: 1
 Column_name: orderby
   Collation: A
 Cardinality: 200
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
2 rows in set (0.00 sec)

mysql> explain extended select * from t1 order by orderby\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6167431
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6167431
        Extra: Using filesort
1 row in set (0.00 sec)

id is primary key .. sort by id should use primary, orderby is key .. same thing .. 

Suggested fix:
.
[26 May 2009 20:13] Sveta Smirnova
Thank you for the report.

Verified as described.

Looks like duplicate of bug #35334, but "select * from t1 order by orderby;" uses filesort for InnoDB as well since version 5.1.
[26 May 2009 20:14] Sveta Smirnova
test case for the testsuite

Attachment: bug45095.test (application/octet-stream, text), 1.05 KiB.

[28 Sep 2009 18:08] Susanne Ebrecht
Bogdan,

what is up now here?
[29 Oct 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 Nov 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".