mysql ORDER BY with CASE - 太慢，更快的方法？

Question

查看 various answers for ORDER BY with CASE like this one ，我看到我在这个遗留应用程序中被迫做的很可能是专家方法；但是，当行数不多时(100,000 行或更多行导致页面加载时间为 10 秒)，它就太慢了。

请注意，原始查询旨在解决一个明显常见的问题，即查询分析师需要空排序的日期，而不是通常的排序方式。在这种情况下，datefirstprinted 将降序排列，但所有未打印的记录都应填充到列表的顶部。

Original Query 解决了这个问题，但问题的重点是避免派生列 notprintedyet 带来的 filesort 性能损失.

Original Query

SELECT SQL_NO_CACHE
  id, daterun, datefirstprinted,
  case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end as notprintedyet
FROM
  patientrecords
WHERE
  dateuploaded <> '0000-00-00 00:00:00'
ORDER BY
  notprintedyet desc,                                 /* ordered via alias */
  datefirstprinted desc
LIMIT 10;

时间1.52s

---

我发现不对别名 notprintedyet 进行排序可以节省一点:

Slightly Faster Query

SELECT SQL_NO_CACHE
  id, daterun, datefirstprinted,
  case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end as notprintedyet
FROM
  patientrecords
WHERE
  dateuploaded <> '0000-00-00 00:00:00'
ORDER BY
  datefirstprinted = "0000-00-00 00:00:00" desc,      /* directly ordered */
  datefirstprinted
LIMIT 10;

时间1.37s

---

Optimal Speed, but missing required sorting of empty dates first

SELECT SQL_NO_CACHE
  id, daterun, datefirstprinted,
  case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end as notprintedyet
FROM
  patientrecords
WHERE
  dateuploaded <> '0000-00-00 00:00:00'
ORDER BY                        
  datefirstprinted                                     /* not ordered properly */
LIMIT 10;

时间0.48s

---

I tried using a view

create view notprinted_patientrecords as (
   SELECT id, daterun, datefirstprinted, case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end notprintedyet
   FROM patientrecords
   WHERE dateuploaded <> '0000-00-00 00:00:00'
);

不幸的是当我运行 explain

 explain select * from notprinted_patientrecords order by notprintedyet desc limit 10;

它表明我仍在使用 filesort 并花费了 1.51s 也就是完全没有节省

---

Would it be faster if datefirstprinted default is NULL?

也许吧，但在这个旧版应用中，这可能比页面加载时间额外增加 5 秒造成的危害更大

---

What else might we try? Stored procedures? Functions?

---

更新

As suggested @strawberry - ORDER BY CASE

...
ORDER BY                        
  case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end, datefirstprinted
LIMIT 10;

时间1.52s

---

根据@e4c5 的要求，explain 输出:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: patientrecords
         type: range
possible_keys: dateuploaded,uploads_report
          key: dateuploaded
      key_len: 5
          ref: NULL
         rows: 299095
        Extra: Using index condition; Using filesort

未正确排序除外，它具有以下差异

        rows: 10
        Extra: Using where

建表语句

*************************** 1. row ***************************
Table: patientrecords
Create Table: CREATE TABLE `patientrecords` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `datecreated` datetime NOT NULL,
  `dateuploaded` datetime NOT NULL,
  `daterun` datetime NOT NULL,
  `datebilled` datetime NOT NULL,
  `datefirstprinted` datetime NOT NULL,
  `datelastprinted` datetime NOT NULL,
  `client` varchar(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `dateuploaded` (`dateuploaded`),
  KEY `daterun` (`daterun`),
  KEY `uploads_report` (`dateuploaded`,`client`),
  KEY `datefirstprinted` (`datefirstprinted`),
  KEY `datelastprinted` (`datelastprinted`)
)

Answer 1

看你的表，首先要注意的是下面的索引是多余的

KEY `dateuploaded` (`dateuploaded`),

它的作用可以由这个来完成

KEY `uploads_report` (`dateuploaded`,`client`),

所以让我们删除 dateuploaded 键。不清楚您是否在任何查询中实际使用了客户列。如果你不这样做，我相信按如下方式更改你的索引会给你一个很大的加速

KEY `uploads_report` (`dateuploaded`,`datefirstprinted`,`client`),

这是因为mysql每张表只能使用一个索引。由于 dateuploaded 列上的索引正在 where 子句中使用，因此不能使用 datefirstprinted 的索引。但是，如果您将这两列合并到同一个索引中，它就可以同时用于排序和位置。

在你做了上面的索引之后，这个可能会被删除:

KEY `datefirstprinted` (`datefirstprinted`),

索引越少，插入和更新越快。