List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:March 10 2011 7:51pm
Subject:Re: Help with slow query
View as plain text  

On 3/10/11 10:46 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)

This last one can be HUGE. I tracked a big performance issue to this exact
problem - the columns used in the join had the same name, but different
data types. Correcting to be the same type (both ints) made a terrific
performance increase.



-- 
Andy Wallace
iHOUSEweb, Inc.
awallace@stripped
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
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