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.robin...@psmnv.com]
> Sent: Friday, May 10, 2013 11:36 AM
> To: Rick James; Bruce Ferrell; mysql@lists.mysql.com
> 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@lists.mysql.com. 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/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
