List:General Discussion« Previous MessageNext Message »
From:Sergei Petrunia Date:August 23 2012 6:53pm
Subject:Re: view query is slow
View as plain text  
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
Thread
view query is slowJames W. McNeely23 Aug
  • RE: view query is slowRick James23 Aug
  • RE: view query is slowMartin Gainty23 Aug
    • Re: view query is slowJames W. McNeely23 Aug
      • Re: view query is slowJames W. McNeely23 Aug
  • Re: view query is slowShawn Green23 Aug
    • Re: view query is slowJames W. McNeely23 Aug
  • Re: view query is slowSergei Petrunia23 Aug