On 10.05.2007 13:59, nk wrote:
> 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? Is there a way to tell MySQL to order rows using a certain
> index?

Did you try a covering index on (dateLast, status) or (status, dateLast)?

robert