MySQL ORDER BY optimisation on range

Question

Hello, I'd like MySQL to use the index to sort these rows.

SELECT
  identity_ID
FROM
  identity
WHERE
  identity_modified > 1257140905
ORDER BY
  identity_modified

However, this is using a filesort for sorting (undesirable).

Now, if I leave off the ORDER BY clause here, the rows come out sorted simply as a consequence of using the index to satisfy the WHERE clause.

So, I can get the behaviour I want by leaving off the WHERE clause, but then I'm relying on MySQL's behaviour to be consistent for the rows to arrive in order, and might get stung in future simply if MySQL changes its internal behaviour.

What should I do? Any way of telling MySQL that since the index is stored in order (b-tree) that it doesn't need a filesort for this?

The table looks like this (simplified):

CREATE TABLE IF NOT EXISTS `identity` (
  `identity_ID`       int(11) NOT NULL auto_increment,
  `identity_modified` int(11) NOT NULL,
  PRIMARY KEY         (`identity_ID`),
  KEY                 `identity_modified` (`identity_modified`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

Answer 1

If you change the table type to INNODB there won't be a filesort

ALTER TABLE mydb.identity ENGINE = INNODB;

But I'm not sure there's a problem with your query: http://forums.mysql.com/read.php?24,10738,10785#msg-10785

Run your original query with EXPLAIN before and after the operation.

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

Answer 2

The solution I have so far is to leave off the ORDER BY clause, and add a FORCE INDEX (identity_modified) to the query. This should in theory ensure that the rows are returned in the order they are stored in the index.

It's probably not a best practice way to do it, but it seems to be the only way that works the way I want.