Mysql
MySql - 如何改進此查詢?
我有下表: http: //pastebin.com/XH6FvhZ8
和以下查詢:
SELECT * FROM (SELECT * FROM `job_logs` ORDER BY id DESC) AS job_logs_tmp WHERE (job_logs_tmp.user_id = ? AND job_logs_tmp.workflow_state = ?) GROUP BY chain, subchain, job_type
這是這個查詢的解釋:http: //pastebin.com/AjXgzRBA
這個查詢通常需要超過 4K ms 才能執行,有什麼想法可以加快查詢執行速度嗎?如果需要,我可以在表中添加索引沒有問題
謝謝!
你想在這里達到什麼目的?我不是 MySQL 程序員;但是,根據我對 SQL 的一般理解,未在 GROUP BY 子句中列出的每一列都必須是聚合的一部分,例如
SELECT MAX(id) AS max_id, chain, subchain, job_type FROM `job_logs` WHERE user_id = ? AND workflow_state = ? GROUP BY chain, subchain, job_type
SELECT *
在大多數(如果不是全部)SQL 實現中,在分組查詢中將不起作用。更新
為了獲得每個組的 id 最高的行,您必須將上面的查詢嵌入到“外部”查詢中。
SELECT * FROM `job_logs` WHERE id IN ( SELECT MAX(id) AS max_id FROM `job_logs` WHERE user_id = ? AND workflow_state = ? GROUP BY chain, subchain, job_type )
此查詢是確定性的,應該適用於大多數 SQL 方言。
一些查詢引擎使用連接比使用“IN 子查詢”執行得更好。你可以試試這個
SELECT A.* FROM `job_logs` A INNER JOIN ( SELECT MAX(id) AS max_id FROM `job_logs` WHERE user_id = ? AND workflow_state = ? GROUP BY chain, subchain, job_type) B ON A.id = B.max_id;
嘗試這個:
SELECT * FROM ( SELECT * FROM `job_logs` WHERE user_id = ? AND workflow_state = ? ORDER BY id DESC ) tmp GROUP BY chain, subchain, job_type