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
