Thanks for the responses to everyone! Here is the result for the explains. ============view query============= explain select * from admin_exam_view where dateexam = '2012-08-13'; +----+-------------+-------+------+-----------------+------------+---------+----------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+------------+---------+----------------------------+--------+-------------+ | 1 | SIMPLE | p | ALL | DOB | NULL | NULL | NULL | 281123 | Using where | | 1 | SIMPLE | e | ref | IdPatient | IdPatient | 99 | RIS_archive.p.IdPatient | 6 | Using where | | 1 | SIMPLE | ec | ref | Quantity,IdExam | IdExam | 138 | RIS_archive.e.IdExam | 3 | | | 1 | SIMPLE | c | ref | IdApptType | IdApptType | 51 | func | 1 | | | 1 | SIMPLE | a | ref | IdAppt | IdAppt | 99 | RIS_archive.e.IdAppt | 1 | Using where | | 1 | SIMPLE | af | ref | IdAffil | IdAffil | 93 | RIS_archive.c.IdPractAffil | 1 | | +----+-------------+-------+------+-----------------+------------+---------+----------------------------+--------+-------------+
=============direct query============= +----+-------------+-------+------+--------------------------------+------------+---------+----------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+--------------------------------+------------+---------+----------------------------+--------+-------------+ | 1 | SIMPLE | p | ALL | IdPatient_UNIQUE,IdPatient,DOB | NULL | NULL | NULL | 281123 | Using where | | 1 | SIMPLE | e | ref | IdPatient,statusnumber,IdAppt | IdPatient | 99 | RIS_archive.p.IdPatient | 6 | Using where | | 1 | SIMPLE | a | ref | PRIMARY,zzk,IdAppt | IdAppt | 99 | RIS_archive.e.IdAppt | 1 | Using where | | 1 | SIMPLE | ec | ref | Quantity,IdExam | IdExam | 138 | RIS_archive.e.IdExam | 3 | | | 1 | SIMPLE | c | ref | IdApptType | IdApptType | 51 | func | 1 | | | 1 | SIMPLE | af | ref | IdAffil | IdAffil | 93 | RIS_archive.c.IdPractAffil | 1 | | +----+-------------+-------+------+--------------------------------+------------+---------+----------------------------+--------+-------------+ I can't tell any practical difference between the two. Jim McNeely On Aug 23, 2012, at 12:39 PM, Shawn Green wrote: > On 8/23/2012 2:30 PM, 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? >> > > Look at the two EXPLAINs. I believe that when you run the query directly, you > get to optimize that term into the execution of the view. When you run it > through the view, the ALGORITHM is set to force the view to materialize all > of the rows in the query, then scan those to find the rows that match your > condition. > > When you execute the query manually, you are getting the benefits of > peformance as you would have for ALGORITHM=MERGE in the VIEW. However since > you are not getting those benefits, it looks like you are in an > ALGORITHM=TEMPTABLE situation. > > http://dev.mysql.com/doc/refman/5.5/en/view-algorithms.html > > The explain plans will clearly show which situation you are in. > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > Office: Blountville, TN > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql >
