Hi,
I have a MyISAM table T with the following schema:
f1 (integer unsigned not null) f2 (integer unsigned not null)
This table has an index on f2 and it currently contains 320 million rows, and is expected to grow at the rate of about 200,000 rows once a week. I perform the following query on this table:
SELECT DISTINCT T.f1 FROM T WHERE f2=@Var LIMIT ?,30
@Var is a variable passed to the stored procedure which executes this query, and the LIMIT variable changes according to the page number being shown (starting at 0, etc)
The speed of retrieval is very good (considering that the table is very large) but the rows appear in the order in which they were written to the table (i.e. not in f1 order). I would like to be able to include the clause "ORDER BY f1 DESC" in the above query, however, doing this without an INDEX would be suicidal! (sometimes there may be over a million rows which satisfy the query and ordering them without an index would probably kill the server)
My question is...what index(es) should be present to cater for the query I am running and also for the ordering of the rows in the result?
Thanks in advance, Tim