List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:March 10 2011 6:46pm
Subject:Re: Help with slow query
View as plain text  
On 3/10/2011 13:12, Jim McNeely wrote:
> Shawn,
>
> This is the first thing that I though as well, but here is a portion from the show
> create table for patient_:
>
>
> PRIMARY KEY (`zzk`),
>   KEY `IdPatient` (`IdPatient`),
>   KEY `SSN` (`SSN`),
>   KEY `IdLastword` (`IdLastword`),
>   KEY `DOB` (`DateOfBirth`),
>   KEY `NameFirst` (`NameFirst`),
>   KEY `NameLast` (`NameLast`)
>
> This extremely simple join is still massively slow.
>
> Jim
>
> On Mar 10, 2011, at 10:00 AM, Shawn Green (MySQL) wrote:
>
>> On 3/10/2011 12:32, Jim McNeely wrote:
>>> 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';
>>>

1) Verify that the indexes on `patient_` haven't been disabled

SHOW INDEXES FROM `patient_`;

http://dev.mysql.com/doc/refman/5.5/en/show-index.html

2) Verify that the data types of `Appt_`.`IdPatient` and 
`patient_`.`IdPatient` are not incompatible. (for example: one is 
varchar, the other int)

Thanks,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
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