Mysql: order by two column, use filesort

I have trouble ordering two columns.

EXPLAIN SELECT * FROM articles WHERE option <>0 AND deleted=0 ORDER BY
    date_added DESC, category_id DESC LIMIT 25 OFFSET 500

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE articles ALL NULL NULL NULL NULL 437168 Using where; Using filesort

I add single indexes for (option, deleted, date_added, category_id)

When i used:

EXPLAIN SELECT * FROM articles WHERE option <>0 AND deleted=0 ORDER BY
    date_added DESC LIMIT 25 OFFSET 500

EXPLAIN SELECT * FROM articles WHERE option <>0 AND deleted=0 ORDER BY
    category_id DESC LIMIT 25 OFFSET 500

Using only where

I tried add index to (option, deleted, date_added, category_id) but it works only when i try sort by one column.

Solution

It will be very hard to get MySQL to use an index for this query:

SELECT *
FROM articles
WHERE option <> 0 AND deleted = 0
ORDER BY date_added DESC
LIMIT 25 OFFSET 500

You can try a composite index: articles(deleted, date_added, option). By covering the WHERE and ORDER BY, MySQL might use it.

If you can add an optionflag column for equality testing (rather than <>), then write the query as:

SELECT *
FROM articles
WHERE optionflag = 1 AND deleted = 0
ORDER BY date_added DESC
LIMIT 25 OFFSET 500;

Then an index on articles(deleted, optionflag, date_added desc) would work well.

Otherwise a subquery might work for you:

SELECT a.*
FROM (SELECT *
      FROM articles
      WHERE deleted = 0
      ORDER BY date_added DESC
     ) a
WHERE option <> 0
LIMIT 25 OFFSET 500;

This materializes the intermediate result, but it is doing an order by anyway. And, the final ordering is not guaranteed to be surfaced in the outer query, but it does work in practice (and is close to guaranteed because of the materialization).