On 8/23/2012 2:30 PM, James W. McNeely wrote:
> I am working on a view based on this query:
> -- Patient Info
> p.IdLastword MRN,
> -- Provider Info
> -- Exam Info
> a.WorkArea dept,
> a.Room location,
> 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
> 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
The explain plans will clearly show which situation you are in.
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN