This simple, single table query does a filesort, why?

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) 

Replay

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 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.

Category: mysql Time: 2016-07-30 Views: 3

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.121 (s). 12 q(s)