Mysql – This simple, single table query does a filesort, why

MySQLoptimization

The table:

CREATE TABLE `test` (
  `status` tinyint(4) NOT NULL,
  `type` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
  `created` int(11) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  KEY `status` (`status`,`type`,`created`,`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

the query

EXPLAIN 
SELECT * 
FROM test 
WHERE status = 1 AND type = 'discussion'
ORDER BY created DESC, title ASC
LIMIT 10 OFFSET 0\G

the result

  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: status
          key: status
      key_len: 35
          ref: const,const
         rows: 1716
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

Best Answer

ORDER BY a ASC, b ASC and
ORDER BY a DESC, b DESC
can avoid "filesort" (if they can use INDEX(a,b)). But if you mix ASC and DESC, it cannot avoid a sort pass.

And, no, you cannot say INDEX(a ASC, b DESC). Or rather, you can say it, but DESC is ignored in the index definition.

Note that "filesort" may or may not actually touch disk. In many cases, the sort can be done entirely in RAM. What you are probably concerned about is the need to scan the entire table when you want only the first 'page'.

(This seems to be a watered down case. If you would like to present the real schema, there might be other tips.)

Suggestion: Use DESC for both fields and hint to the user that the dates were really different when he sees two titles in DESC order.

Since you seem to be doing "pagination", see how to remember where you left off as an optimization for subsequent pages.

Related Question