使用 Group by 進行慢速 MySQL 查詢

我有一個查詢需要很長時間才能執行幾分鐘甚至更多。訂單和票這兩個表有大約 15 萬條記錄。這是查詢：

SELECT 
 orders.id,
 count(distinct tickets.id) as attached_tickets 
from
 orders
 LEFT JOIN tickets ON tickets.order_id = orders.id
GROUP BY
 orders.id  
LIMIT 1;

我發現通過刪除 ORDER BY 語句，它可以加快幾秒鐘的速度。這些是 EXPLAIN 的結果

+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | orders  | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL | 121291 |   100.00 | Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | tickets | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   7347 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+

是的，我在兩個表中都有 id 索引。有什麼辦法可以加快查詢速度嗎？

這會快很多，但有缺陷：

SELECT  id, 
     ( SELECT  count(*)
           FROM  tickets
           WHERE  order_id = o.id 
     ) as attached_tickets
   FROM  orders AS o
   LIMIT  1;

它將需要INDEX(order_id)在tickets.

• 你想要哪個order.id？沒有ORDER BY說LIMIT 1會選擇哪一個。
• 這可能不能很好地概括。你真正的查詢是什麼？

引用自：https://dba.stackexchange.com/questions/138568