Ordering Rows Using Index

[MrManager]

Is there a way to tell MySQL to order rows using a certain index?

I'm running the following query on a table with an index on dateLast, and one on status:

SELECT field FROM table
WHERE (DATEDIFF(NOW(), dateLast) > 7) AND (status = 1)
ORDER BY dateLast ASC
LIMIT 10

This query takes about 80 seconds, because MySQL uses the index on 'status', which means it has to sort the rows using filesort.

When I remove 'status' from the query:

SELECT field FROM table
WHERE (DATEDIFF(NOW(), dateLast) > 7)
ORDER BY dateLast ASC
LIMIT 10

The query doesn't even take a second, because MySQL can use the index on dateLast to both check the where condition and sort the rows.

Any ideas?

[phranque]

instead if 2 indices try using a multiple-column index on dateLast, status.

CREATE INDEX date_status ON table (dateLast, status);
DROP INDEX dateLast ON table;
DROP INDEX status ON table;