List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 10 2013 8:20pm
Subject:RE: Slow Response -- What Does This Sound Like to You?
View as plain text  
A bit more readable:

select  (mod(dayofweek(`Query1`.`Appointment_Date`)+7-1,7)),
     {fn CONCAT({fn CONCAT({fn CONCAT(cast(extract(hourfrom 
`Time_Difference_Query`.`Created_Date`) as char(25)),':')}, 
     cast(extract(minute from  `Time_Difference_Query`.`Created_Date`) as
char(25)))},':')}, 
      `Time_Difference_Query`.`Created_Date`,
        `Query1`.`Appointment_Provider_Name`
    from  
        ( SELECT  distinct `EMR_ENCOUNTER`.`encType` as "Encounter_Type" ,
                case when `EMR_ENCOUNTER`.`encLock` = 0 then 'UnLocked' else 'Locked' end
as "Chart_Lock_Status" ,
                `EMR_ENCOUNTER`.`notesDoneTime` as "Notes_Done_Time" ,
                `EMR_ENCOUNTER`.`dateOut` as "Notes_Done_Date" ,
                `EMR_ENCOUNTER`.`timeIn` as "Appointments_Checked_In" ,
                `EMR_ENCOUNTER`.`timeOut` as "Appointments_Checked_Out" ,
                `EMR_ENCOUNTER`.`depTime` as "Appointments_Departure_Time" ,
                `EMR_ENCOUNTER`.`arrivedTime` as "Appointments_Arrived_Time" ,
                `EMR_ENCOUNTER`.`endTime` as "Appointment_End_Time" ,
                `EMR_ENCOUNTER`.`startTime` as "Appointment_Start_Time" ,
                `EMR_ENCOUNTER`.`date` as "Appointment_Date" ,
                `EMR_ENCOUNTER`.`encounterID` as "Encounter_ID" ,
                `EDI_FACILITIES`.`Name` as "Facility_Name" ,
                `APPOINTMENT_PROVIDER`.`uid` as "Appointment_Provider_ID" ,
                {fn CONCAT({fn CONCAT({fn CONCAT({fn
CONCAT(`APPOINTMENT_PROVIDER`.`ulname`,', ')},
               `APPOINTMENT_PROVIDER`.`ufname`)}, ' ')},
                `APPOINTMENT_PROVIDER`.`uminitial`)} as "Appointment_Provider_Name"
            from  (`enc` `EMR_ENCOUNTER`
            LEFT OUTER JOIN  `edi_facilities` `EDI_FACILITIES` on
`EMR_ENCOUNTER`.`facilityId` = `EDI_FACILITIES`.`Id`)
            LEFT OUTER JOIN  (`doctors` `APPOINTMENT_DOCTOR`
            INNER JOIN  `users` `APPOINTMENT_PROVIDER` on `APPOINTMENT_DOCTOR`.`doctorID`
= `APPOINTMENT_PROVIDER`.`uid`)
                 on `EMR_ENCOUNTER`.`doctorID` = `APPOINTMENT_DOCTOR`.`doctorID`
            where  `EMR_ENCOUNTER`.`encType` = 2
              and  `EMR_ENCOUNTER`.`date` between cast('2011-01-01' as date) and
cast('2013-05-07' as date)
              and  `EMR_ENCOUNTER`.`patientID` <> 8663
              and  `EMR_ENCOUNTER`.`VisitType` <> 'PTDASH'
              and  `EMR_ENCOUNTER`.`deleteFlag` = 0
              and  `APPOINTMENT_PROVIDER`.`UserType` = 1
              and  `APPOINTMENT_PROVIDER`.`delFlag` = 0
              and  `EDI_FACILITIES`.`DeleteFlag` = 0
        ) `Query1`
    LEFT OUTER JOIN  
        ( SELECT  distinct `Addressed_Query`.`moddate` as "Locked_Date" ,
                `Created_Query`.`moddate` as "Created_Date" ,
                `Created_Query`.`encounterid` as "encounterid" ,
                `Created_Query`.`reason` as "reason" , `Created_Query`.`Patient_Name` as
"Patient_Name"
            from  
                ( SELECT  distinct `SQL1`.`moddate` as "moddate" , `SQL1`.`encounterid` as
"encounterid" ,
                        `SQL1`.`actionflag` as "actionflag" , `SQL1`.`ufname` as "ufname"
,
                        `SQL1`.`ulname` as "ulname" , `SQL1`.`reason` as "reason" ,
                        {fn CONCAT({fn CONCAT(`SQL1`.`ulname`, ', ')},
                        `SQL1`.`ufname`)} as "Patient_Name"
                    from  
                        ( SELECT  users.ufname,users.ulname,cast(reason as char(30)) as
reason,
                                telenc_loghist.actionflag,telenc_loghist.encounterid,
                               telenc_loghist.moddate
                            from  telenc_loghist
                            inner join  enc on enc.encounterid=telenc_loghist.encounterid
                            inner join  users on users.uid=enc.patientid
                            where  actionflag in(0)
                              and  enc.date between '2011-01-01' and '2013-05-07') `SQL1`
                ) `Created_Query`
            LEFT OUTER JOIN  
                ( SELECT  distinct `Q2`.`moddate` as "moddate" , `Q2`.`encounterid` as
"encounterid" ,
                        `Q2`.`actionflag` as "actionflag"
                    from  
                        ( SELECT  telenc_loghist.actionflag,telenc_loghist.encounterid,
                               telenc_loghist.moddate
                            from  telenc_loghist
                            inner join  enc on enc.encounterid=telenc_loghist.encounterid
                            where  actionflag in(4)
                              and  enc.date between '2011-01-01' and '2013-05-07') `Q2`
                ) `Addressed_Query` on `Created_Query`.`encounterid` =
`Addressed_Query`.`encounterid`
            where  NOT `Addressed_Query`.`moddate` is null
      ) `Time_Difference_Query`
               on `Query1`.`Encounter_ID` = `Time_Difference_Query`.`encounterid`
    where  `Query1`.`Appointment_Provider_ID` in (9118, 9119, 60922, 9116, 47495)
      and  `Query1`.`Facility_Name` in ('Fremont Family Care')
      and  `Query1`.`Appointment_Date` between cast(cast('2011-01-01' as date) as date)
                                           and cast(cast('2013-05-07' as date) as date)
      and  `Query1`.`Appointment_Provider_ID` = 60922;
-----------

The big problem is 
   FROM ( SELECT ... ) JOIN ( SELECT ... ) ON ...
Neither of those subqueries has an index, so there will be "table scans".  The solution is
to CREATE TEMPORARY TABLE ... SELECT for each one, then add an index.

You SELECT a bunch of rows as Query1, then filter??  Can't you move the filtering into the
subquery??

There is no need for either CAST in cast(cast('2013-05-07' as date) as date); simply use
'2013-05-07'.

What does the {} syntax do?? 

Contradictory:
    where  `Query1`.`Appointment_Provider_ID` in (9118, 9119, 60922, 9116, 47495)
      and  `Query1`.`Appointment_Provider_ID`               = 60922;
The IN filter does nothing useful.

I think those changes will make the query run _much_ faster.  If not, provide the SHOW
CREATE TABLE for the tables being used here, plus EXPLAIN SELECT.

> -----Original Message-----
> From: Robinson, Eric [mailto:eric.robinson@stripped]
> Sent: Friday, May 10, 2013 11:36 AM
> To: Rick James; Bruce Ferrell; mysql@stripped
> Subject: RE: [>Suspected Spam<][Characteristics] RE: Slow Response --
> What Does This Sound Like to You?
> 
> >
> > 1. MyISAM locks _tables_.  That can cause other connections to be
> > blocked.  Solution: switch to InnoDB.  Caution:  There are a few
> > caveats when switching; see
> > https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/
> >
> > 2. As mentioned by Shawn, the Query Cache can be more trouble than it
> > is worth.  However 90 seconds cannot be blamed on the QC.  Still,
> > shrink it or turn it off:
> > * If frequently writing to tables, turn it off (type=OFF _and_
> size=0)
> > * If less frequently, then decide which queries will benefit, add
> > SQL_CACHE to them, set type=DEMAND and size=50M (no larger).
> >
> > 3. Meanwhile, try to make that long query more efficient.
> > Can you show it to us, together with SHOW CREATE TABLE, SHOW TABLE
> > STATUS, and EXPLAIN ?
> >
> 
> Thanks for the feedback, Rick.
> 
> There are 1200+ tables in the database, so I don't think you want a
> SHOW CREATE TABLE, SHOW TABLE STATUS, EXPLAIN for all of them. :-) The
> query in question is always some variation of the following. From
> looking at this, which table(s) would you like to see this information
> for?
> 
> 
> # Time: 130507 18:14:26
> # User@Host: site150_DbUser[site150_DbUser] @ cognos08.mycharts.md
> [192.168.10.85] # Query_time: 82  Lock_time: 0  Rows_sent: 1
> Rows_examined: 914386 select
> (mod(dayofweek(`Query1`.`Appointment_Date`)+7-1,7)), {fn CONCAT({fn
> CONCAT({fn CONCAT(cast(extract(hour from
> `Time_Difference_Query`.`Created_Date`) as char(25)), ':')},
> cast(extract(minute from `Time_Difference_Query`.`Created_Date`) as
> char(25)))}, ':')}, `Time_Difference_Query`.`Created_Date`,
> `Query1`.`Appointment_Provider_Name` from (select distinct
> `EMR_ENCOUNTER`.`encType` as "Encounter_Type" , case  when
> `EMR_ENCOUNTER`.`encLock` = 0 then 'UnLocked' else 'Locked' end  as
> "Chart_Lock_Status" , `EMR_ENCOUNTER`.`notesDoneTime` as
> "Notes_Done_Time" , `EMR_ENCOUNTER`.`dateOut` as "Notes_Done_Date" ,
> `EMR_ENCOUNTER`.`timeIn` as "Appointments_Checked_In" ,
> `EMR_ENCOUNTER`.`timeOut` as "Appointments_Checked_Out" ,
> `EMR_ENCOUNTER`.`depTime` as "Appointments_Departure_Time" ,
> `EMR_ENCOUNTER`.`arrivedTime` as "Appointments_Arrived_Time" ,
> `EMR_ENCOUNTER`.`endTime` as "Appointment_End_Time" ,
> `EMR_ENCOUNTER`.`startTime` as "Appointment_Start_Time" ,
> `EMR_ENCOUNTER`.`date` as "Appointment_Date" ,
> `EMR_ENCOUNTER`.`encounterID` as "Encounter_ID" ,
> `EDI_FACILITIES`.`Name` as "Facility_Name" ,
> `APPOINTMENT_PROVIDER`.`uid` as "Appointment_Provider_ID" , {fn
> CONCAT({fn CONCAT({fn CONCAT({fn
> CONCAT(`APPOINTMENT_PROVIDER`.`ulname`, ', ')},
> `APPOINTMENT_PROVIDER`.`ufname`)}, ' ')},
> `APPOINTMENT_PROVIDER`.`uminitial`)} as "Appointment_Provider_Name"
> from (`enc` `EMR_ENCOUNTER` LEFT OUTER JOIN `edi_facilities`
> `EDI_FACILITIES` on `EMR_ENCOUNTER`.`facilityId` =
> `EDI_FACILITIES`.`Id`) LEFT OUTER JOIN (`doctors` `APPOINTMENT_DOCTOR`
> INNER JOIN `users` `APPOINTMENT_PROVIDER` on
> `APPOINTMENT_DOCTOR`.`doctorID` = `APPOINTMENT_PROVIDER`.`uid`) on
> `EMR_ENCOUNTER`.`doctorID` = `APPOINTMENT_DOCTOR`.`doctorID` where
> `EMR_ENCOUNTER`.`encType` = 2 and `EMR_ENCOUNTER`.`date` between
> cast('2011-01-01' as date) and cast('2013-05-07' as date) and
> `EMR_ENCOUNTER`.`patientID` <> 8663 and `EMR_ENCOUNTER`.`VisitType` <>
> 'PTDASH' and `EMR_ENCOUNTER`.`deleteFlag` = 0 and
> `APPOINTMENT_PROVIDER`.`UserType` = 1 and
> `APPOINTMENT_PROVIDER`.`delFlag` = 0 and `EDI_FACILITIES`.`DeleteFlag`
> = 0) `Query1` LEFT OUTER JOIN (select distinct
> `Addressed_Query`.`moddate` as "Locked_Date" ,
> `Created_Query`.`moddate` as "Created_Date" ,
> `Created_Query`.`encounterid` as "encounterid" ,
> `Created_Query`.`reason` as "reason" , `Created_Query`.`Patient_Name`
> as "Patient_Name"  from (select distinct `SQL1`.`moddate` as "moddate"
> , `SQL1`.`encounterid` as "encounterid" , `SQL1`.`actionflag` as
> "actionflag" , `SQL1`.`ufname` as "ufname" , `SQL1`.`ulname` as
> "ulname" , `SQL1`.`reason` as "reason" , {fn CONCAT({fn
> CONCAT(`SQL1`.`ulname`, ', ')}, `SQL1`.`ufname`)} as "Patient_Name"
> from (select users.ufname,users.ulname,cast(reason as char(30)) as
> reason,
> telenc_loghist.actionflag,telenc_loghist.encounterid,telenc_loghist.mod
> date from telenc_loghist
>                      inner join enc on
> enc.encounterid=telenc_loghist.encounterid
>                      inner join users on users.uid=enc.patientid where
> actionflag in(0) and enc.date between '2011-01-01' and '2013-05-07')
> `SQL1`) `Created_Query` LEFT OUTER JOIN (select distinct `Q2`.`moddate`
> as "moddate" , `Q2`.`encounterid` as "encounterid" , `Q2`.`actionflag`
> as "actionflag"  from (select
> telenc_loghist.actionflag,telenc_loghist.encounterid,telenc_loghist.mod
> date from telenc_loghist
>                      inner join enc on
> enc.encounterid=telenc_loghist.encounterid where actionflag in(4) and
> enc.date between '2011-01-01' and '2013-05-07') `Q2`) `Addressed_Query`
> on `Created_Query`.`encounterid` = `Addressed_Query`.`encounterid`
> where  NOT `Addressed_Query`.`moddate` is null) `Time_Difference_Query`
> on `Query1`.`Encounter_ID` = `Time_Difference_Query`.`encounterid`
> where `Query1`.`Appointment_Provider_ID` in (9118, 9119, 60922, 9116,
> 47495) and `Query1`.`Facility_Name` in ('Fremont Family Care') and
> `Query1`.`Appointment_Date` between cast(cast('2011-01-01' as date) as
> date) and cast(cast('2013-05-07' as date) as date) and
> `Query1`.`Appointment_Provider_ID` = 60922;
> 
> 
> 
> Also, the query cache size is currently set to 64MB. Do you really
> think turning it off is a good idea?
> 
> The my.cnf file follows...
> 
> [mysqld_safe]
> timezone=America/Chicago
> [mysqld]
> #-optimization
> skip-locking
> skip-innodb
> skip-bdb
> query_cache_type = 1
> tmp_table_size=1M
> wait_timeout=2048
> interactive_timeout=2048
> table_cache=1024
> query_cache_limit=1M
> thread_concurrency = 4
> key_buffer = 256M
> query_cache_size=64M
> max_allowed_packet = 1M
> sort_buffer_size = 512K
> read_buffer_size = 512K
> myisam_sort_buffer_size = 128M
> thread_cache_size=40
> max_connections=500
> max_tmp_tables=32
> lower_case_table_names=1
> #-turn on query logging
> #log=/ha01_mysql/site150/mysql/query.log
> log_slow_queries=/ha01_mysql/site150/mysql/slow_query.log
> long_query_time=3
> #-make this server a replication master
> #log-bin = /ha01_mysql/site150/mysql/binlogs/
> expire_logs_days=5
> server-id=99
> #replicate-do-db=mobiledoc_150
> 
> 
> I'm thinking that our best solution may be to re-enable the slave
> (currently disabled) and point the nasty query at it.
> 
> By the way, we have considered switching to innodb, but there the
> advantages of MyISAM in our environment have usually outweighed.
> 
> --Eric
> 
> 
> 
> 
> 
> 
> Disclaimer - May 10, 2013
> This email and any files transmitted with it are confidential and
> intended solely for Rick James,Bruce Ferrell,mysql@stripped. If
> you are not the named addressee you should not disseminate, distribute,
> copy or alter this email. Any views or opinions presented in this email
> are solely those of the author and might not represent those of
> Physicians' Managed Care or Physician Select Management. Warning:
> Although Physicians' Managed Care or Physician Select Management has
> taken reasonable precautions to ensure no viruses are present in this
> email, the company cannot accept responsibility for any loss or damage
> arising from the use of this email or attachments.
> This disclaimer was added by Policy Patrol:
> http://www.policypatrol.com/
Thread
Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
  • RE: Slow Response -- What Does This Sound Like to You?Rick James9 May
    • RE: Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
      • RE: Slow Response -- What Does This Sound Like to You?Wm Mussatto9 May
        • RE: Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
          • Re: Slow Response -- What Does This Sound Like to You?shawn green9 May
      • Re: Slow Response -- What Does This Sound Like to You?Bruce Ferrell10 May
        • RE: Slow Response -- What Does This Sound Like to You?Rick James10 May
          • RE: [>Suspected Spam<][Characteristics] RE: Slow Response -- What Does This Sound Like to You?Eric Robinson10 May
            • RE: Slow Response -- What Does This Sound Like to You?Rick James10 May
  • Re: Slow Response -- What Does This Sound Like to You?Johnny Withers9 May
  • Re: Slow Response -- What Does This Sound Like to You?Denis Jedig9 May
RE: Slow Response -- What Does This Sound Like to You?Vikas Shukla10 May