Shawn, Thanks for the great help! It still is not working. I did an EXPLAIN on this query with your amended split out join statements and got this:
+----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ | 1 | SIMPLE | a | range | apptdate | apptdate | 4 | NULL | 296148 | Using where | | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 262462 | | | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 311152 | | | 1 | SIMPLE | c | ref | IdApptType | IdApptType | 51 | func | 1 | | | 1 | SIMPLE | af | ALL | NULL | NULL | NULL | NULL | 5680 | | +----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ What I'm not catching is why it says there is no key it can use for the patient table; here is a portion of the show create: PRIMARY KEY (`zzk`), KEY `IdPatient` (`IdPatient`), KEY `SSN` (`SSN`), KEY `IdLastword` (`IdLastword`), KEY `DOB` (`DateOfBirth`), KEY `NameFirst` (`NameFirst`), KEY `NameLast` (`NameLast`) So, the IdPatient is at least a POSSIBLE key, right? On Mar 9, 2011, at 8:34 PM, Shawn Green (MySQL) wrote: > 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=j...@newcenturydata.com >
