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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
