MySQL uses filesort on indexed TIMESTAMP column

Question

I've got a table that refuses to use index, and it always uses filesort.

The table is:

CREATE TABLE `article` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Category_ID` int(11) DEFAULT NULL,
  `Subcategory` int(11) DEFAULT NULL,
  `CTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Publish` tinyint(4) DEFAULT NULL,
  `Administrator_ID` int(11) DEFAULT NULL,
  `Position` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `Subcategory` (`Subcategory`,`Position`,`CTimestamp`,`Publish`),
  KEY `Category_ID` (`Category_ID`,`CTimestamp`,`Publish`),
  KEY `Position` (`Position`,`Category_ID`,`Publish`),
  KEY `CTimestamp` (`CTimestamp`),
  CONSTRAINT `article_ibfk_1` FOREIGN KEY (`Category_ID`) REFERENCES `category` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=94290 DEFAULT CHARSET=utf8

The query is:

SELECT * FROM article ORDER BY `CTimestamp`;

The explain is:

+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL | 63568 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+

When I remove the "ORDER BY" then all are working properly. All other indices (Subcategory, Position, etc) are working fine in other queries. Unfortunately, the timestamp refuses to be used, even with my simple select query. I'm sure I'm missing something important here.

How can I make MySQL use the timestamp index?

Thank you.

What error you are getting when you are trying to create an index and place the code with which you have tried to create index (In my network ajax was blocked i cant comment over there that's why i am asking here). — Shabarinath Volam

mvp mvp · Accepted Answer · 2012-10-25T08:06:04

In this case, MySQL is not using your index for sorting, and it is a GOOD thing. Why? Your table contains just 64k rows, average row width is about 26 bytes (if I added column sizes right), so total table size on disk should be around 2MB. It is very cheap to read just 2MB of data from disk into memory (probably in just 1-2 disk operations or seeks) and then simply perform filesort in memory (probably variation of quicksort).

If MySQL did retrieval by index order as you wish, it would have to perform 64000 disk seek operations, one record after another! It would have been very, very slow.

Indexes can be good when you can use them to quickly jump to known location in huge file and read just small amount of data, like in WHERE clause. But, in this case, it is not good idea - and MySQL is not stupid!

If your table was very big (more than RAM size), then MySQL would certainly start using your index - and this is also good thing.

MySQL uses filesort on indexed TIMESTAMP column

2 Answers