CODEWITHSUNDEEP
mysql
Manic Depression
Manic Depression

Reputation: 1030

MySQL - avoid filesort

I stuck with filesort on my query

EXPLAIN SELECT o.job_offerid
FROM job_offer AS o
WHERE o.status = 1
ORDER BY o.sequence ASC, o.active_updated_when DESC, o.created DESC
LIMIT 10;

This query uses right index, but stil uses filesort. Output from EXPLAIN:

select_type: SIMPLE
table: o
type: ref
possible_keys: status,status_sequence_active_updated_when_created   
key: status_sequence_active_updated_when_created
key_len: 4
ref: const
rows: 15153
Extra: Using where; Using filesort

Type of columns are:

status int(13), sequence int(13), active_updated_when (datetime), created (datetime)

Table MyISAM, MySQL version 5.6.21

I try to look at similar questions on stackoverflow, but without success.

Thank you

Edit (add table structure without other columns):

CREATE TABLE `job_offer` (
  `job_offerid` int(13) NOT NULL AUTO_INCREMENT,
  `sequence` int(13) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `status` int(13) NOT NULL DEFAULT '1',
  `active_updated_when` datetime DEFAULT NULL,
  PRIMARY KEY (`job_offerid`),
  KEY `status` (`status`),
  KEY `sequence` (`sequence`),
  KEY `created` (`created`),
  KEY `active_updated_when` (`active_updated_when`),
  KEY `status_sequence_active_updated_when_created` (`status`,`sequence`,`active_updated_when`, `created`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 185

Answers (1)

rlanvin
rlanvin

Reputation: 6267

You can't avoid filesort in this case.

Why? Because of this:

In some cases, MySQL cannot use indexes to resolve the ORDER BY

[...]

You mix ASC and DESC

Best you can do at this point if you want to completely avoid filesort is to rethink your ORDER BY clause. Is it really necessary to have all those 3 fields? Can you refactor your table to use less fields for sorting? Alternatively, can you change the meaning of one field to use only DESC (or only ASC) for all?

Upvotes: 2

Related Questions