List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:March 10 2011 4:34am
Subject:Re: Help with slow query
View as plain text  
Hi Jim,

On 3/9/2011 17:57, Jim McNeely wrote:
> I am trying to set up an export query which is executing very slowly, and I was
> hoping I could get some help. Here is the query:
>
> SELECT a.IdAppt, a.IdPatient,
> p.NameLast, p.NameFirst, p.NameMI,
> a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, " ", a.ApptTimeOut) AS CHAR)
> ApptDateTime, a.ApptLenMin Duration,
> a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type,
> t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9,
> '??????' Diagnosis_free_test
>
> from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af)
> ON (a.IdPatient = p.IdPatient
> AND a.IdPatientDate = t.IdPatientDate
> AND CONCAT(a.IdAppt, '0') = c.IdApptType
> AND a.IdPriCarePhy = af.IdAffil)
> WHERE a.ApptDate>= '2009-03-01';
>
> p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all indexed. Also I
> selectively took out join parameters until there was nothing but a join on the patient
> table, and it was still slow, but when I took that out, the query was extremely fast. What
> might I be doing wrong?
>
> Thanks,
>
> Jim McNeely

The performance problem is with your Cartesian product. I think you 
meant to write:

from Appt_ a
LEFT JOIN patient_ p
   ON a.IdPatient = p.IdPatient
LEFT JOIN today_ t
   ON a.IdPatientDate = t.IdPatientDate
LEFT JOIN Copy_ c
   ON CONCAT(a.IdAppt, '0') = c.IdApptType
LEFT JOIN Affil_ af
   ON a.IdPriCarePhy = af.IdAffil

As of 5.0.12, the comma operator for table joins was demoted in the 
'order of precedence' for query execution. That means that MySQL became 
more complaint with the SQL standard but it also means that using a 
comma-join instead of an explicit ANSI join can result in a Cartesian 
product more frequently.

Try my style and compare how it works. If both styles are similarly 
slow, collect the EXPLAIN plan for this query and share with the list.

Yours,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Help with slow queryJim McNeely10 Mar
  • Re: Help with slow queryMySQL)10 Mar
    • Re: Help with slow queryJim McNeely10 Mar
Re: Help with slow queryJim McNeely10 Mar
  • Re: Help with slow querymos10 Mar
  • Re: Help with slow queryMySQL)10 Mar
    • Re: Help with slow queryJim McNeely10 Mar
      • Re: Help with slow queryMySQL)10 Mar
        • Re: Help with slow queryAndy Wallace10 Mar