有人能告诉我为什么这个查询需要很长时间才能运行吗？

Question

SELECT PatientID as 'PatientIDIX', claims.ClaimID as 'ClaimIDIX', ClaimThroughDate as 'ClaimThroughDateIX', HCPCCode, ProcedureID
FROM `t1`.revenue revenue   
JOIN `t1`.patient patient ON revenue.ClaimID = patient.ClaimID   
JOIN `t1`.claims claims ON claims.ClaimID = revenue.ClaimID
LEFT JOIN `t1`.procedures procedures ON procedures.ClaimID = revenue.ClaimID
WHERE HCPCCode IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
OR ProcCode IN (11, 22, 33, 44, 55, 66, 77, 88, 99, 1010);

我想知道有没有人知道为什么上面的查询花了这么长时间。我相信这是因为左连接。谢谢!

Answer 1

我找到了一些可能对你有帮助的文章：

• http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/

- [http://www.slideshare.net/techdude/how-to-kill-mysql-performance](http://www.slideshare.net/techdude/how-to-kill-mysql-performance)
- [MYSQL OR vs IN performance](https://stackoverflow.com/questions/782915/mysql-or-vs-in-performance)

​

根据您的数据、索引和其他因素，最简单的解决方案可能是使用临时表，而不是使用IN语句。