I've got a bit of a problem. I'm supposed to port an existing nicely optimized database over from MSSQL to MySQL. So now with Subquery capabilities in MySQL that's at least an option... the problem is they are about a billion times SLOWER in MySQL. (and unfortunately I'm not exagerating)
Is there a syntax issue I'm missing?
Database tables are as follows:
Patients (this has a single record for each patient)
Evals (this has multiple records for each patient id. )
(pretty darn simple ey?)
Goal: I need to return the Total number of unique patients with 2 or more entries in the evals table that fall within a certain Patient_ID range.
Query: --------------------------------------------------
SELECT COUNT(*)
FROM Patients
WHERE Key_ID IN
(
SELECT DISTINCT Evals.Patient_ID FROM Evals
INNER JOIN Patients ON Patients.Key_ID = Evals.Patient_ID
WHERE (Evals.Location_ID = 2)
AND (Patients.Center_ID = 2)
AND (Patients.Key_ID >= 820)
GROUP BY Evals.Patient_ID
HAVING (COUNT(Evals.Patient_ID) > 1))
)
------------------------------------------------------------------
The same exact query runs in the following amount of time:
MSSQL response time: 0.01 seconds
MySQL 4.1 response time: 78 seconds.
Is there another way to write this query? Is there a bug in MySQL? Is there a workaround in MySQL? Any suggestions would be happily appreciated.
Linear Mode
