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