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