List:General Discussion« Previous MessageNext Message »
From:James W. McNeely Date:August 23 2012 9:05pm
Subject:Re: view query is slow
View as plain text  
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
> 


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