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=1
>
