[thelist] MySQL optimizaton problem

[Gijs van Tulder]

Hi,

I've got a small but difficult MySQL optimization problem. It seems that my
ORDER BY queries always lead to a filesort, no matter what indexes I create.

To test this problem, I created this small table with some test data in it:
CREATE TABLE test (
  id tinyint(4) NOT NULL auto_increment,
  sort tinyint(4) NOT NULL default '0',
  PRIMARY KEY (id),
  KEY sort (sort)
) TYPE=MyISAM;

+----+------+
| id | sort |
+----+------+
|  1 |    2 |
|  2 |    4 |
|  3 |    2 |
|  4 |    3 |
|  5 |   10 |
+----+------+

Now, I'd like to sort on id. I run the following query:
EXPLAIN SELECT * FROM test ORDER BY sort;
+-------+------+---------------+------+---------+------+------+-------------
---+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra
|
+-------+------+---------------+------+---------+------+------+-------------
---+
| test  | ALL  | NULL          | NULL |    NULL | NULL |    5 | Using
filesort |
+-------+------+---------------+------+---------+------+------+-------------
---+

Because there are no indexes, MySQL has to use the slower filesort to sort
the rows. Then I add an index on column sort:
ALTER TABLE test ADD INDEX sort (sort);

Now, according to the MySQL-manual, MySQL should use the index to sort the
rows. It doesn't, but instead still uses filesort.
EXPLAIN SELECT * FROM test ORDER BY sort;
+-------+------+---------------+------+---------+------+------+-------------
---+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra
|
+-------+------+---------------+------+---------+------+------+-------------
---+
| test  | ALL  | NULL          | NULL |    NULL | NULL |    5 | Using
filesort |
+-------+------+---------------+------+---------+------+------+-------------
---+

While this is a simple example, it also happens when I'm using larger tables
where filesort isn't the optimal way to sort my queries. Could anyone please
explain what I'm doing wrong?

Thanks,

Gijs