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=j...@newcenturydata.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
