On Thu, Aug 23, 2012 at 11:30:17AM -0700, James W. McNeely wrote:
> 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?
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
temporary table.
However, I don't see a reason why a view for the above query would be
materialized. It should be merged.
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
