List:General Discussion« Previous MessageNext Message »
From:Jim McNeely Date:March 10 2011 5:32pm
Subject:Re: Help with slow query
View as plain text  
Rhino,

Thanks for the help and time! Actually, I thought the same thing, but what's weird is that
is the only thing that doesn't slow it down. If I take out all of the join clauses EXCEPT
that one the query runs virtually instantaneously. for some reason it will use the index
in that case and it works. If I take out everything like this:

SELECT a.IdAppt, a.IdPatient, 
p.NameLast, p.NameFirst, p.NameMI

from Appt_ a
LEFT JOIN patient_ p
 ON a.IdPatient = p.IdPatient
WHERE a.ApptDate >= '2009-03-01';

It is still utterly slow. EXPLAIN looks like this:

+----+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   |
Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+
|  1 | SIMPLE      | a     | range | apptdate      | apptdate | 4       | NULL | 296166 |
Using where |
|  1 | SIMPLE      | p     | ALL   | NULL          | NULL     | NULL    | NULL | 262465 | 
           |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-------------+

But, very good try. I thought this might be it as well.

Thanks,

Jim McNeely

On Mar 10, 2011, at 9:05 AM, Rhino wrote:

> 
> What I'm about to say may be completely out to lunch so don't be afraid to dismiss
> it. I'm more a DB2 guy than a MySQL guy and I'm getting rusty on both but I've always been
> struck by how similar the two dtabases are. Therefore, I want to offer an insight on why
> this query would not perform terribly well in DB2. I simply don't know if it is applicable
> to MySQL.
> 
> In DB2, using functions on predicates (conditions in a WHERE clause), prevents DB2
> from using an index to satisfy that predicate. (Or at least it used to: I'm not certain if
> that has been remedied in recent versions of the DB2 optimizer.) Therefore, the CONCAT()
> function in the line
> "AND CONCAT(a.IdAppt, '0') = c.IdApptType" would ensure that no index on the IdAppt
> column would be used to find the rows of the table that satisfied that condition.
> 
> My suggestion is that you try rewriting that condition to avoid using CONCAT() - or
> any other function - and see if that helps the performance of your query. That would
> require modifying your data to append a zero to the end of the existing date in IdApptType
> column, which may or may not be a reasonable thing to do. You'll have to decide about
> that.
> 
> Again, I could be all wet here so don't have me killed if I'm wrong about this :-)
> I'm just trying to help ;-)
> 
> --
> Rhino
> 
> On 2011-03-10 11:38, Jim McNeely wrote:
>> 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