php - MySQL INNER 与 LEFT JOIN 不同的顺序

Question

为什么这两个查询具有相同的 ORDER BY 却返回不同的结果集。

查询的唯一区别是我第一次使用 INNER JOIN 时大约需要 5 秒。第二次我使用 LEFT JOIN，用了 0.05 秒。在这两种情况下，它们都恰好返回 43.000 行，但 tck.id 顺序不同，我无法弄清楚为什么或以何种方式？

SELECT tck.*, acc.ac_name
FROM support_tickets tck  
INNER JOIN support_ticket_accounts acc USING (id_support_ticket_account)
WHERE tck.id_company = 2 AND tck.st_status = 1 ORDER BY tck.st_priority DESC

编辑:

SELECT tck.*, acc.ac_name
FROM support_tickets tck
    LEFT JOIN support_ticket_accounts acc ON tck.id_support_ticket_account = acc.id_support_ticket_account
WHERE tck.id_company = 2 AND tck.st_status = 1
ORDER BY tck.st_priority DESC;
+----+-------------+-------+--------+---------------+------------+---------+-------------------------------+-------+-----------------------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref                           | rows  | Extra                       |
+----+-------------+-------+--------+---------------+------------+---------+-------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | tck   | ref    | id_company    | id_company | 5       | const                         | 37586 | Using where; Using filesort |
|  1 | SIMPLE      | acc   | eq_ref | PRIMARY       | PRIMARY    | 4       | tck.id_support_ticket_account |     1 |                             |
+----+-------------+-------+--------+---------------+------------+---------+-------------------------------+-------+-----------------------------+

SELECT tck.*, acc.ac_name
FROM support_tickets tck
    INNER JOIN support_ticket_accounts acc ON tck.id_support_ticket_account = acc.id_support_ticket_account
WHERE tck.id_company = 2 AND tck.st_status = 1
ORDER BY tck.st_priority DESC;
+----+-------------+-------+------+-------------------------------------+----------------------------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys                        | key                       | key_len | ref                           | rows | Extra                           |
+----+-------------+-------+------+--------------------------------------+---------------------------+---------+-------------------------------+------+---------------------------------+
|  1 | SIMPLE      | acc   | ALL  | PRIMARY                              | NULL                      | NULL    | NULL                          |    5 | Using temporary; Using filesort |
|  1 | SIMPLE      | tck   | ref  | id_company,id_support_ticket_account | id_support_ticket_account | 5       | acc.id_support_ticket_account | 2085 | Using where                     |
+----+-------------+-------+------+--------------------------------------+---------------------------+---------+-------------------------------+------+---------------------------------+

Answer 1

我认为using temporary对延迟负责(但我不明白为什么一个查询有必要而不是另一个查询)。我想创造 multi-column index应该有帮助:

CREATE INDEX filter 
    ON support_tickets(id_company, st_status, st_priority)
    USING BTREE;