List:General Discussion« Previous MessageNext Message »
From:Jim McNeely Date:March 10 2011 4:38pm
Subject:Re: Help with slow query
View as plain text  
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
> 


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