使用filesort按MySQL中的datetime列进行排序 [英] Using filesort to sort by datetime column in MySQL

问题描述

我有一张带有日期时间（DATE）和位（PUBLIC）的汽车。

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

现在我想采用DATE订购的行并使用PUBLIC = 1我使用：

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

但不幸的是，当我使用explain来查看发生了什么时，我有这个：

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

在我只有100行的情况下获取此数据需要0.3 ms。还有其他方法可以禁用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?

如果我去索引我的索引（PUBLIC，DATE）不是唯一的。

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

表格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 ;

推荐答案

你需要在<$ c上有一个复合索引$ c>（公开，日期）

这样， MySQL 将过滤 public 并按日期排序。

从 EXPLAIN 我看到你在（公共，日期）上没有复合索引。

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

相反，您在 public 和 date 上有两个不同的索引。至少，这就是他们的名字 IDX_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.

更新：

您公众列不是 BIT ，它是 BINARY（1）。它是一个字符类型并使用字符比较。

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

当将整数与字符进行比较时， MySQL 将后者转换为前者反之亦然。

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

更改 public 列为位或重写您的查询：

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。即将字符与字符进行比较，而不是整数。

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

这篇关于使用filesort按MySQL中的datetime列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！