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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
