MySQL Bugs: #45095: order by not using index

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

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:
.

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.

test case for the testsuite

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

Bogdan,

what is up now here?

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".

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".