Using filesort to sort by datetime column in MySQL

Question

hello,

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 c
WHERE 
   c.PUBLIC = 1
ORDER 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 ;

Answer 1

If you are ordering by date, a sort will be required. If there isn't an index by date, then a filesort will be used. The only way to get rid of that would be to either add an index on date or not do the order by.

Also, a filesort does not always imply that the file will be sorted on disk. It could be sorting it in memory if the table is small enough or the sort buffer is large enough. It just means that the table itself has to be sorted.

Looks like you have an index on date already, and since you are using PUBLIC in your where clause, MySQL should be able to use that index. However, the optimizer may have decided that since you have so few rows it isn't worth bothering with the index. Try adding 10,000 or so rows to the table, re-analyze it, and see if that changes the plan.

Answer 2

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);

INSERT
INTO    t_binary
VALUES
(1),
(2),
(3),
(10);

SELECT  *
FROM    t_binary
WHERE   val <= 10;

---
1
2
3
10

SELECT  *
FROM    t_binary
WHERE   val <= '10';
---
1
10

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

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

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