Getting rid of a filesort.

[mattal999]

I'm trying to optimise some queries that seem to be using temporary tables and filesorts, and stumbled across this nasty one. I have the following query:

 

SELECT * FROM `logs` ORDER BY `datetime` DESC LIMIT 0, 50

 

Here's the table structure:

 

CREATE TABLE IF NOT EXISTS `logs` (
  `id` mediumint(12) unsigned NOT NULL AUTO_INCREMENT,
  `userid` mediumint(10) unsigned NOT NULL,
  `objectid` mediumint(10) unsigned NOT NULL,
  `datetime` datetime NOT NULL,
  `action` varchar(100) NOT NULL,
  `reason` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `datetime` (`datetime`)
) ENGINE=MyISAM ;

 

Now, by my understanding, that query should use the datetime key. But instead, it decides to do a filesort.

 

id	select_type	table		type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE		logs		ALL	NULL		NULL	NULL	NULL	11	Using filesort

 

What am I doing wrong?

[The Little Guy]

Your problem is that you should add a where clause, using datetime.

 

http://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/

 

So, the conclusion:

- You can avoid the filesort by making order by column appear in the where clause

- When using join, make sure the left side join table column is used in the ORDER BY clause or change the join type

[mattal999]

Thanks for your reply. That article is interesting, and I've tried it but it doesn't seem to work...

 

Here's the ammended query:

 

SELECT * FROM `logs` WHERE `datetime` > '1970-01-01 00:00:00' ORDER BY `datetime` DESC LIMIT 0, 50

 

And this results in:

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE		logs	ALL	datetime	NULL	NULL	NULL	11	Using where; Using filesort

 

I'll give the join idea a whirl soon, but as far as I can see, that should have worked...

[The Little Guy]

is there any reason you need everything?

 

If you could select less items, you might be able to remove that file sort

[mattal999]

Well, I need to select everything because I want the 50 most recent rows from that table.

 

EDIT: Funny thing is:

 

SELECT * FROM  `logs` FORCE INDEX ( DATETIME ) ORDER BY  `datetime` DESC LIMIT 0 , 50

 

Outputs:

 

id	select_type	table	type	possible_keys	key		key_len	ref	rows	Extra
1	SIMPLE		logs	index	NULL		datetime	8	NULL	11	 

[fenway]

It says there are only 11 rows total -- is that true?

[mattal999]

It says there are only 11 rows total -- is that true?

 

Indeed, and now I've just realised why it wanted to use a filesort instead. It was actually more efficient than using the index. I added another 100 rows and now it works perfectly.

 

Thanks for your help anyway guys.