MySQL 說明：使用索引、使用臨時、使用文件排序。這個查詢可以改進嗎？

我正在開發一個事件跟踪系統，它使用一些查找表以及主日誌記錄表。在我正在編寫的報告中，可以選擇一個對象來查看統計數據。該界面按重要性遞減的順序顯示所有對象（即命中）。

兩個表的架構（略微精簡，但你明白了要點）：

CREATE TABLE IF NOT EXISTS `event_log` (
 `event_id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(5) DEFAULT NULL,
 `object_id` int(5) DEFAULT NULL,
 `event_date` datetime DEFAULT NULL,
 PRIMARY KEY (`event_id`),
 KEY `user_id` (`user_id`),
 KEY `object_id` (`object_id`)
);

CREATE TABLE IF NOT EXISTS `lookup_event_objects` (
 `object_id` int(11) NOT NULL AUTO_INCREMENT,
 `object_desc` varchar(255) NOT NULL,
 PRIMARY KEY (`object_id`)
);

我遇到問題的查詢如下。它適用於我的約 100 個條目的表，但 EXPLAIN 讓我有點擔心。

   explain SELECT 
           el.object_id, 
           leo.object_desc, 
           COUNT(el.object_id) as count_rows
       FROM 
           event_log el 
           LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
       GROUP BY 
           el.object_id
       ORDER BY 
           count_rows DESC,
           leo.object_desc ASC

回報： Using index; Using temporary; Using filesort

temporary那麼——我的架構和/或查詢 MySQL 有什麼問題filesort？還是使用 ORDER BY 可以得到優化？

這是您的原始查詢

SELECT 
   el.object_id, 
   leo.object_desc, 
   COUNT(el.object_id) as count_rows
FROM 
   event_log el 
   LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY 
   el.object_id
ORDER BY 
   count_rows DESC,
   leo.object_desc ASC
;

看起來好像已經優化到了

我會改變你的查詢

SELECT 
   el.object_id, 
   leo.object_desc, 
   SUM(ISNULL(leo.object_id)=0) as count_rows
FROM 
   event_log el 
   LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY 
   el.object_id
ORDER BY 
   count_rows DESC,
   leo.object_desc ASC
;

這可能會讓您獲得更準確的計數，尤其是對於沒有查找對象的事件日誌。

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