# Mysql not using index for ORDER BY ?

**URL:** https://forums.percona.com/t/mysql-not-using-index-for-order-by/318
**Category:** Other MySQL® Questions
**Created:** [May 12, 2007, 4:17am UTC](https://forums.percona.com/t/mysql-not-using-index-for-order-by/318 "2007-05-12T04:17:03Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![arnoooooo](https://avatars.discourse-cdn.com/v4/letter/a/bcef8e/32.png) [@arnoooooo](https://forums.percona.com/u/arnoooooo)
#### Post date: [May 12, 2007, 4:17am UTC](https://forums.percona.com/t/mysql-not-using-index-for-order-by/318/1 "2007-05-12T04:17:03Z")

</div>

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 )

---

<div class="post-metadata">

### Author: ![carpii](https://avatars.discourse-cdn.com/v4/letter/c/b5ac83/32.png) [@carpii](https://forums.percona.com/u/carpii)
#### Post date: [May 13, 2007, 12:11pm UTC](https://forums.percona.com/t/mysql-not-using-index-for-order-by/318/2 "2007-05-13T12:11:06Z")

</div>

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

---

<div class="post-metadata">

### Author: ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)
#### Post date: [May 13, 2007, 3:45pm UTC](https://forums.percona.com/t/mysql-not-using-index-for-order-by/318/3 "2007-05-13T15:45:08Z")

</div>

| [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](http://infocenter.sybase.com/help/index.jsp?topic=/com.sybas) e.dc34982\_1500/html/mig\_gde/mig\_gde73.htm