当按日期时间排序时，联接繁重的MySQL查询会变慢，而不是按ID排序

Question

我有一个MySQL（实际上是MariaDB）查询，当我尝试按DATETIME字段（sighted）而不是主键排序时，它的运行速度要慢得多，并且会回到文件排序上。我在sighted上有一个索引，但由于某种原因，它似乎没有使用它。我如何为这个表编制索引，以便它可以有效地按DATETIME字段排序？

select 
    user_id , t.name, f.value, y.name 
    from posts p 
        left join taxon_sightings t on (t.post_id = p.id) 
        left join fields f on (f.foreign_id = t.id) 
        left join fieldtypes y on (y.id = f.fieldtype_id) 
    group by p.id 
    order by p.sighted desc 
    limit 10;

当我按sighted DATETIME字段排序时：

MariaDB [dbase]> explain [yadda yadda, as above]
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref                            | rows | Extra                           |
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+---------------------------------+
|  1 | SIMPLE      | p     | ALL    | NULL          | NULL       | NULL    | NULL                           | 1759 | Using temporary; Using filesort |
|  1 | SIMPLE      | t     | ref    | post_id       | post_id    | 4       | dataportal_test.p.id           |   12 |                                 |
|  1 | SIMPLE      | f     | ref    | foreign_id    | foreign_id | 4       | dataportal_test.t.id           |    6 | Using where                     |
|  1 | SIMPLE      | y     | eq_ref | PRIMARY       | PRIMARY    | 4       | dataportal_test.f.fieldtype_id |    1 | Using where                     |
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+---------------------------------+

与我按ID:排序时相比

+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref                            | rows | Extra       |
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | p     | index  | NULL          | PRIMARY    | 4       | NULL                           |    1 |             |
|  1 | SIMPLE      | t     | ref    | post_id       | post_id    | 4       | dataportal_test.p.id           |   12 |             |
|  1 | SIMPLE      | f     | ref    | foreign_id    | foreign_id | 4       | dataportal_test.t.id           |    6 | Using where |
|  1 | SIMPLE      | y     | eq_ref | PRIMARY       | PRIMARY    | 4       | dataportal_test.f.fieldtype_id |    1 | Using where |
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+-------------+

Answer 1

我认为你做不到。我能想到的唯一技巧就是试试这个：

select user_id, t.name, f.value, y.name 
from posts p left join
     taxon_sightings t
     on (t.post_id = p.id) left join
     fields f
     on (f.foreign_id = t.id) left join
     fieldtypes y
     on (y.id = f.fieldtype_id) 
group by p.sighted, p.id 
---------^ very important
order by p.sighted desc 
limit 10;

然后在posts(sighted, id)上包含一个索引。具有order by的group by可能利用该索引。如果你幸运的话。

Answer 2

假设p.id在posts中是唯一的，让我们去掉GROUP BY p.id。我认为它存在的唯一原因是因为JOINs正在夸大行数？

除非你需要，否则扔掉LEFT。

SELECT  x.user_id, t.name, f.value, y.name
    FROM  
      ( SELECT  id, sighted
            FROM  posts
            -- No GROUP BY needed if id is UNIQUE
            ORDER BY  sighted DESC
            LIMIT  10 
      ) AS p
    JOIN  taxon_sightings t ON (t.post_id = p.id)
    JOIN  fields f ON (f.foreign_id = t.id)
    JOIN  fieldtypes y ON (y.id = f.fieldtype_id)
    GROUP BY  p.id
    ORDER BY  p.sighted DESC, p.id DESC;

并且（正如Gordon所建议的），拥有INDEX(sighted, id)。