MySQL Bugs: #7999: Indexes do not work when doing an ORDER BY

Bug #7999	Indexes do not work when doing an ORDER BY
Submitted:	19 Jan 2005 1:21	Modified:	20 Feb 2005 15:44
Reporter:	Beau Frierson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	4.1.9	OS:	Liniux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I have the MySQL Cluster installed on 4 high-end Dell Servers.  My tables have Indexes on their ID's but when I use an ORDER BY clause the query does not use the index.

How to repeat:
EXPLAIN SELECT * FROM table1 ORDER BY tblID /*where tblID is the primary ID*/

| table  | type | possible_keys | key   | key_len | ref     | Rows   | Extra           |
|-------|------|--------------|------|---------|------|--------|--------------|
|Table1 | ALL  | NULL             | NULL | NULL     | NULL | 237849 | Using filesort |

Suggested fix:
make order by clauses use indexes

Hi,

This is actually not a bug :-)
mysqld conciders performing filesort "cheaper" than doing ordered index scan.

You can however force it to use index.
See "The FROM table_references" on http://dev.mysql.com/doc/mysql/en/SELECT.html.

Another make it use index is to add a limit clause.

---

Please try this, and update this bug report accordingly...

/Jonas

the query takes 14+ seconds on our high-end servers. I tried using the same query on a MyISAM table on a low-end testing desktop computer with identecal data, indexes, etc. the query took 0.06 seconds and used the primary key index.

Weird...

Did you try the "use index" or "limit" ?

yes I have tried USE INDEX (PRIMARY), and FORCE INDEX (PRIMARY).  I also have a LIMIT 5000 on the query.  I can drop the ORDER BY statement and it runs in 0.01 seconds.

Hi,

Could you attach your tables with some testdata where the error occurs.
Your config.ini could also maybe help.

I just tested select * from T1 order by 1 limit 1;
When I add the "limit 1" it always uses the ordered index.
Otherwise it performs filesort.
The difference for my table with 250k rows is 0.01sec compared to 12sec.

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