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.

Most obvious question to a most often overlooked solution, when you ported over your database you did index your necessary columns for the join right?

It's fully indexed.

More information:

The Subquery itself (the one in the where statement) takes aproximately .04 seconds to execute.

It's that stupid Count(*) statement using the subquery to feed the Where in ( ) statement that takes the massive amount of item. Which is why I'm wondering if there is another way to write this query.

It's only part of a much larger query so I'm really trying to avoid rewriting the thing into a set of a dozen small queries. That's not really an option time-wise.

So, any ideas on a faster way to get the recordcount of one query squished into a field of another? (which is basically as simple as the query gets)

My guess:

SELECT Evals.Patient_ID, COUNT(*)
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(*) > 1))

Hope this helps!

Iñaki

Yep thanks, that solution works. Still rather annoying to rebuild these queries since that's a very small section of a very large query. It appears impossible to do the same task in one query without that syntax.

Though I did find an interesting correlation. Group By / Having statements in a subquery slow queries by orders of magnitudes. Removing that made the query lightning fast (too bad I still need it )

In anycase, that solution will have to do Thanks for the suggestion.

I don't know if any will assist you....
http://www.mysql.com/news-and-events...000000301.html

Restart the mySQL server process with --log-slow-queries switch enabled

AFAIK "GROUP BY" implies sorting. I'm not sure what might be able to be
tweaked at the session, table or index level that might improve access time.