Can anyone provide me with some insight into why this query is taking a long time to run?

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);

I was wondering if anyone has some insight into why the above query is taking so long. I believe it is because of the left join. Thanks!

Do you have any indexes? Does the query execute if you remove the `WHERE` clause? What table does the WHERE clause columns come from? — Taryn, Dec 11 '15 at 14:39
Your question lacks a lot of details. How long it takes to run, do you have any indexes, how much data is being retrieved and so on... — Andrey Korneyev, Dec 11 '15 at 14:39

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

I found a few articles that may help you:

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
- MYSQL OR vs IN performance

Depending on your data, indexing and other factors, the simplest solution may be to use a temp table instead of using the IN statement.

edited May 23 '17 at 12:15

Community

1
1

answered Dec 11 '15 at 17:22

Vin Vill

24
5

Can anyone provide me with some insight into why this query is taking a long time to run?

1 Answers1