Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Exclamation Unanswered: MySQL Subquery slow problem

    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.

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    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?

  3. #3
    Join Date
    Jun 2004
    Posts
    3

    Indexed right, still stupidly slow

    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)

  4. #4
    Join Date
    May 2004
    Location
    Barcelona, Spain
    Posts
    54
    Quote Originally Posted by TokenSDragon
    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

  5. #5
    Join Date
    Jun 2004
    Posts
    3
    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.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •