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
>
