From: Shawn Green
Date: August 23 2012 7:39pm
Subject: Re: view query is slow
List-Archive: http://lists.mysql.com/mysql/228069
Message-Id: <50368700.3050700@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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