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
EXPLAIN SELECT * FROM test WHERE status = 1 AND type = 'discussion' ORDER BY created DESC, title ASC LIMIT 10 OFFSET 0\G
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)
It's due to your sorting. Run explain without the desc sort.
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
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.)
DESC for both fields and hint to the user that the dates were really different when he sees two titles in
Since you seem to be doing "pagination", see how to remember where you left off as an optimization for subsequent pages.