I am working on a view based on this query:
=======================================
SELECT
-- Patient Info
p.IdPatient,
p.IdLastword MRN,
p.NameLast,
p.NameFirst,
p.Addr1,
p.Addr2,
p.AddrCity,
p.AddrState,
p.AddrZip,
p.Gender,
p.DateOfBirth,
-- Provider Info
af.IdAffil,
af.PractName,
af.OfficeName,
-- Exam Info
e.IdExam,
e.dateexam,
a.WorkArea dept,
a.Room location,
e.ProcModeCode,
e.ProcName,
e.IdRefSite,
ec.IdCPT,
e.zzk exam_zzk,
ec.zzk examcpt_zzk
FROM patient_ p
LEFT JOIN exams e ON e.IdPatient = p.IdPatient
LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity)
LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType
LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt
LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil
WHERE
p.AddrState = 'WA'
AND e.statusnumber = '4'
AND e.IdRefSite <> 'S50'
AND e.IdRefSite <> 'S51'
AND e.IdREfSite <> 'S63'
AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH)
AND a.zzk IS NOT NULL
============================================
If I run this query itself (not in the view), and add this:
AND e.dateexam = '2012-08-13'
it runs like lightning, super fast. But if I run the query against the view, for example
"SELECT * FROM exam_view WHERE dateexam = '2012-08-13'
It is so glacially slow that I end up having to kill the query. What is going on, and how
can I fix this?
Jim McNeely
Northwest Radiologists
Senior Database Programmer
360-788-9022 desk
360-303-3332 mobile
