Mysql – Using filesort to sort by datetime column in MySQL

filesort, indexing, mysql, performance

I have a table Cars with datetime (DATE) and bit (PUBLIC).

Now i would like to take rows ordered by DATE and with PUBLIC = 1 so i use:

select  c.*from  Cars cWHERE    c.PUBLIC = 1ORDER BY    DATE DESC

But unfortunately when I use explain to see what is going on I have this:

1   SIMPLE  a   ALL     IDX_PUBLIC,DATE     NULL    NULL    NULL    103     Using where; Using filesort

And it takes 0,3 ms to take this data while I have only 100 rows. Is there any other way to disable filesort?

If i goes to indexes I have index on (PUBLIC, DATE) not unique.

Table def:

CREATE TABLE IF NOT EXISTS `Cars` (  `ID` int(11) NOT NULL auto_increment,  `DATE` datetime NOT NULL,  `PUBLIC` binary(1) NOT NULL default '0'  PRIMARY KEY  (`ID`),  KEY `IDX_PUBLIC` (`PUBLIC`),  KEY `DATE` (`PUBLIC`,`DATE`)) ENGINE=MyISAM  AUTO_INCREMENT=186 ;

Best Solution

You need to have a composite index on (public, date)

This way, MySQL will filter on public and sort on date.

From your EXPLAIN I see that you don't have a composite index on (public, date).

Instead you have two different indexes on public and on date. At least, that's what their names IDX_PUBLIC and DATE tell.

Update:

You public column is not a BIT, it's a BINARY(1). It's a character type and uses character comparison.

When comparing integers to characters, MySQL converts the latter to the former, not vice versa.

These queries return different results:

CREATE TABLE t_binary (val BINARY(2) NOT NULL);INSERTINTO    t_binaryVALUES(1),(2),(3),(10);SELECT  *FROM    t_binaryWHERE   val <= 10;---12310SELECT  *FROM    t_binaryWHERE   val <= '10';---110

Either change your public column to be a bit or rewrite your query as this:

SELECT  c.*FROM    Cars cWHERE   c.PUBLIC = '1'ORDER BY         DATE DESC

, i. e. compare characters with characters, not integers.