List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:March 10 2011 6:00pm
Subject:Re: Help with slow query
View as plain text  
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';
>
> 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.
>
... snip ...

According to this report, there are no indexes on the `patient_` table 
that include the column `IdPatient` as the first column. Fix that and 
this query should be much faster.

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