That is an example of where VIEWs screw up optimizations. Rumor has it that 5.6.6 might have improvements.
Probably inefficient: ON CONCAT(e.IdAppt , '0') = c.IdApptType p might benefit from INDEX(AddrState, DateOfBirth) SHOW CREATE TABLE (for each table) EXPLAIN SELECT (with and without VIEW) We might have more comments/suggestions. > -----Original Message----- > From: James W. McNeely [mailto:j...@newcenturydata.com] > Sent: Thursday, August 23, 2012 11:30 AM > To: mysql@lists.mysql.com > Subject: view query is slow > > 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 > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
