CODEWITHSUNDEEP
mysql
alquist42
alquist42

Reputation: 749

MySQL Order by index still using FileSort

I have some testimonials table

CREATE TABLE IF NOT EXISTS `testimonials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `user` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','test','inactive','') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `t_order` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t_order` (`t_order`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=26 ;

And some simple task: enable manual sorting.

The SELECT query looks:

mysql> EXPLAIN SELECT t_order, id, title, description FROM testimonials WHERE status = 'active' ORDER BY t_order DESC;

The output:

| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra                       |

|  1 | SIMPLE      | testimonials | ALL  | NULL          | NULL | NULL    | NULL |   23 | Using where; Using filesort |

ORDER BY uses index field, but still EXPLAIN shows Using filesort.

Why a sort can’t be performed from an index? Something with the query?

Thanks)

Upvotes: 0

Views: 1565

Answers (1)

Mihai
Mihai

Reputation: 26784

The key column shows the index used and it`s null. Using where only means you are using a WHERE to restrict the rows.For your query

ALTER TABLE testimonials ADD KEY(status,t_order )

is the best index,assuming you have enough rows so an index will make sense.(For very few rows a table scan is faster than an index.)

Upvotes: 1

Related Questions