List:General Discussion« Previous MessageNext Message »
From:Rick James Date:August 23 2012 7:26pm
Subject:RE: view query is slow
View as plain text  
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:jim@stripped]
> Sent: Thursday, August 23, 2012 11:30 AM
> To: mysql@stripped
> 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

Thread
view query is slowJames W. McNeely23 Aug
  • RE: view query is slowRick James23 Aug
  • RE: view query is slowMartin Gainty23 Aug
    • Re: view query is slowJames W. McNeely23 Aug
      • Re: view query is slowJames W. McNeely23 Aug
  • Re: view query is slowShawn Green23 Aug
    • Re: view query is slowJames W. McNeely23 Aug
  • Re: view query is slowSergei Petrunia23 Aug