[thelist] MySQL optimizaton problem

[Ashok Hariharan]

Hello Gijs:

I am not very familiar with MySQL, but with most other
databases indexes are not used for ordering data (with an
order by). The data is physically stored in no particular
order.

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

What you need is a 'Where' clause which takes into account a
column that is part of the index

If you have something like
Select * from test where id < 200, and if there is an index
on the id column your query will be faster

>EXPLAIN SELECT * FROM test ORDER BY sort;

Additionally, if MySQL supports clustered indexes (where the index itself
holds the data) you could try creating a clustered index on the sort
column,
it might speed up your particular select query

>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?

HTH

ashok

------
Ashok Hariharan
listman(at)hazard0us.org
http://www.unganisha.org
------