MySQL Performance: MySQL => Mysql not using index for ORDER BY ?

Mysql not using index for ORDER BY ? [message #1240]

Sat, 12 May 2007 05:47

arnoooooo
Messages: 3
Registered: May 2007

Junior Member

Hi all,

I am having trouble with a query on a large (39m rows) table : MySQL won't use the index, for the ORDER BY clause, and creates a temporary table, which takes a lot of disk space and time.

EXPLAIN SELECT * FROM histos_backtest ORDER BY id;

+----+-------------+-----------------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows     | Extra          |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+----------------+
|  1 | SIMPLE      | histos_backtest | ALL  | NULL          | NULL | NULL    | NULL | 39252003 | Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+----------------+

SHOW INDEX FROM histos_backtest;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| histos_backtest |          0 | PRIMARY  |            1 | id          | A         |    39252003 |     NULL | NULL   |      | BTREE      | NULL    |
| histos_backtest |          1 | id       |            1 | id          | A         |    39252003 |     NULL | NULL   |      | BTREE      | NULL    |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

I created an index on the same column as the primary key just to make sure... I use Sybase at work and sometimes, the primary key is not enough. Apparently it is not the case with MySQL.

Any help would be greatly appreciated Smile

Report message to a moderator