Mysql not using index for ORDER BY ?

MySQL & MariaDB Other MySQL® Questions
1

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 )

2

Whats the schema for this table?

Youre right about mysql by the way, a PK in mysql is a unique constraint and also an index.

In MSSQL (and presumably sybase), a PK is only a constraint, which is usually accompanied by an independant clustered index

3
[B]carpii wrote on Sun, 13 May 2007 19:41[/B]
In MSSQL (and presumably sybase), a PK is only a constraint, which is usually accompanied by an independant clustered index
Is this something with older versions?

I’m not a mssql or sybase expert but as far as I know (or have read), when you define a column as primary key both is implicitly creating a unique index on it.

And all databases that I mostly have worked with does it (mysql, postgresql, oracle).

This due to:

  1. You basically always need a index to get decent performance for the unique constraint.
  2. You very often use the primary key for selecting and then you benefit from an index anyway.

Ref:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybas e.dc34982_1500/html/mig_gde/mig_gde73.htm