mysql datetime 排序,使用filesort按MySQL中的datetime列进行排序

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 ;

解决方案

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.