On Thu, Aug 23, 2012 at 11:30:17AM -0700, James W. McNeely wrote:
> I am working on a view based on this query:
> -- Patient Info
> p.IdLastword MRN,
> -- Provider Info
> -- Exam Info
> a.WorkArea dept,
> a.Room location,
> 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
> 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?
What does EXPLAIN show, for both queries? Are they different?
I don't see any obvious reason why query with the VIEW should be slower.
One possible reason why queries through a VIEW are slow is that the view
is materialized into a temporary table (look for 'DERIVED' in EXPLAIN output)
and condition e.dateexam = '2012-08-13' is only applied when reading from the
However, I don't see a reason why a view for the above query would be
materialized. It should be merged.
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org