Huge query, two same servers, one is fast the other is slow

Question

I have a really strange thing. Two servers with the exact same configuration give 2 different performance for the same query (huge query). On is 700ms, the other one is 10sec !

The problem is around "left join shyfts_detail sd on sd.shyft_id = shyfts.id", on one server it uses the key 'shyft_id' (-> rows 1) and on the slow one it don't use a key and the rows are +1985651 (big!)

How to force a key ?

explain select
s.department_id, d.account_id, s.id as id, s.shyft_id as parentShift, 'CLOCKIN' as shyftType, s.ressource_record_id,
rpg.user_type,
if(
(select count(*) from hr_partners_account where account_id=s.account_id and active=1)>1,
rpg.hr_partner,
(select hr_partner_id from hr_partners_account where account_id=s.account_id and active=1 limit 1)
) as hr_partner,
rpg.worktime_calculation_method as user_worktime_calculation_method,
(select worktime_calculation_method from user_category where id=rpg.user_type) as status_worktime_calculation_method,
rpg.hr_partner as hr_partner_user,
WEEKDAY(s.startdate) + 1 as dayNumber,
WEEK(s.startdate,1) as weekNumber,
DATE_FORMAT(s.startdate,'%d') as day,
DATE_FORMAT(s.startdate,'%m') as month,
YEAR(s.startdate) as year,
s.startdate as start,
DATE_FORMAT(s.startdate, '%Y-%m-%d') as startDate,
DATE_FORMAT(s.startdate, '%H:%i') as startHour,
s.enddate as end,
DATE_FORMAT(s.enddate, '%Y-%m-%d') as endDate,
DATE_FORMAT(s.enddate, '%H:%i') as endHour,
if(dpc.paid_clockin_break=1,
(SELECT SUM(IFNULL(TIMESTAMPDIFF(MINUTE, shyfts_clockin_breaks.startdate, shyfts_clockin_breaks.enddate),0)) FROM
shyfts_clockin_breaks WHERE clockin_id=s.id),
'0'
) AS pause_paid,
if(dpc.paid_clockin_break=1,
'0',
(SELECT SUM(IFNULL(TIMESTAMPDIFF(MINUTE, shyfts_clockin_breaks.startdate, shyfts_clockin_breaks.enddate),0)) FROM
shyfts_clockin_breaks WHERE clockin_id=s.id)
) AS pause_unpaid,
sum(TIME_TO_SEC(TIMEDIFF(s.enddate, s.startdate)))/60 as totalWork,
if((SELECT SUM(IFNULL(TIMESTAMPDIFF(MINUTE, shyfts_clockin_breaks.startdate, shyfts_clockin_breaks.enddate),0)) FROM
shyfts_clockin_breaks WHERE clockin_id=s.id)>0
AND dpc.paid_clockin_break=0
,(sum(TIME_TO_SEC(TIMEDIFF(s.enddate, s.startdate)))/60)-(SELECT SUM(IFNULL(TIMESTAMPDIFF(MINUTE,
shyfts_clockin_breaks.startdate, shyfts_clockin_breaks.enddate),0)) FROM shyfts_clockin_breaks WHERE clockin_id=s.id)
,sum(TIME_TO_SEC(TIMEDIFF(s.enddate, s.startdate)))/60) as worktime,
s.skill as skills,
s.section,
(SELECT GROUP_CONCAT(DISTINCT attribute_id ORDER BY attribute_id DESC) FROM shyfts_clockin_attributes where
shyfts_clockin_attributes.clockin_id=s.id) as attributes,
1 as 'shyft_type',
0 as 'dayoffId',
0 as 'hrRealCode',
0 as 'hr_code',
0 as 'dayoff_name',
0 as 'shortcode',
0 as 'hr_shortcode',
0 as 'dayoff_recup',
0 as 'dayoff_vacation',
0 as dayoff_paid,
0 as dayoff_fullday,
d.country,
(select count(*) from publicholidays ph where ph.fulldate = DATE_FORMAT(s.startdate, '%Y-%m-%d') and ph.active=1 and
ph.country=d.country) as public_holiday,
rpg.hr_max_hours_year, rpg.hr_max_hours_week,
(select hours_week from contracts where contracts.ressource_record_id=s.ressource_record_id and
contracts.department_id=s.department_id and (contracts.enddate>=s.startdate or contracts.enddate is null) and
contracts.active=1 and contracts.archived=0 group by contracts.ressource_record_id order by id) as contract_hours_week,
(select days_week from contracts where contracts.ressource_record_id=s.ressource_record_id and
contracts.department_id=s.department_id and (contracts.enddate>=s.startdate or contracts.enddate is null) and
contracts.active=1 and contracts.archived=0 group by contracts.ressource_record_id order by id) as contract_days_week,
rpg.hr_max_hours_month, rpg.hr_max_hours_trimester, rpg.hr_hours_week,
rpg.hr_days_week, rpg.hr_low_flexibility, rpg.hr_high_flexibility,
rpg.hr_dayoff_year, rpg.lang, i18n.shortcode as languageCode,
dpg.default_fulldayhours,
sections.section_name as section_name
from shyfts_clockin s
left join department_params_general dpg on dpg.id = s.department_id
left join ressources_params_general rpg on rpg.ressource_record_id = s.ressource_record_id and
rpg.department_id=s.department_id
left join department d on d.id = s.department_id
left join department_params_clockin dpc on dpc.department_id = s.department_id
left join i18n_language i18n on i18n.id=d.lang
left join sections on sections.id=s.section
where
((s.shyft_id<>'' and s.active=1) or (s.shyft_id='' and s.active=1 and s.freeshift_approved=1))
    and s.startdate>="2022-01-01 00:00:00" and s.startdate
    <"2022-01-31 23:59:59" and s.department_id=1439 
                group by s.id
            UNION
                select
                shyfts.department_id, shyfts.account_id, shyfts.id as id, 0 as parentShift, 'PLANNED' as shyftType, shyfts.ressource_record_id, rpg.user_type,
                if(
                    (select count(*) from hr_partners_account where account_id=shyfts.account_id and active=1)>1,
                    rpg.hr_partner,
                    (select hr_partner_id from hr_partners_account where account_id=shyfts.account_id and active=1 limit 1)
                ) as hr_partner,
                rpg.worktime_calculation_method as user_worktime_calculation_method,
                (select worktime_calculation_method from user_category where id=rpg.user_type) as status_worktime_calculation_method,
                rpg.hr_partner as hr_partner_user,
                    WEEKDAY(shyfts.startdate) + 1 as dayNumber,
                    WEEK(shyfts.startdate,1) as weekNumber,
                    DATE_FORMAT(shyfts.startdate,'%d') as day,
                    DATE_FORMAT(shyfts.startdate,'%m') as month,
                    YEAR(shyfts.startdate) as year,
                    shyfts.startdate as start,
                    DATE_FORMAT(shyfts.startdate, '%Y-%m-%d') as startDate,
                    DATE_FORMAT(shyfts.startdate, '%H:%i') as startHour,
                    shyfts.enddate as end,
                    DATE_FORMAT(shyfts.enddate, '%Y-%m-%d') as endDate,
                    DATE_FORMAT(shyfts.enddate, '%H:%i') as endHour,
                            (SELECT SUM(IFNULL(duration,0)) FROM shyfts_pause WHERE shyft_id=shyfts.id and paid=1) AS pause_paid,
                    (SELECT SUM(IFNULL(duration,0)) FROM shyfts_pause WHERE shyft_id=shyfts.id and paid=0) AS pause_unpaid,
                    (TIME_TO_SEC(TIMEDIFF(shyfts.enddate, shyfts.startdate)))/60 as totalWork,
                            if((SELECT SUM(IFNULL(duration,0)) FROM shyfts_pause WHERE shyft_id=shyfts.id and paid=0)>0,
                        (sum(TIME_TO_SEC(TIMEDIFF(shyfts.enddate, shyfts.startdate)))/60)-(SELECT SUM(IFNULL(duration,0)) FROM shyfts_pause WHERE shyft_id=shyfts.id and paid=0),
                        (TIME_TO_SEC(TIMEDIFF(shyfts.enddate, shyfts.startdate)))/60
                    ) as worktime,
                        (SELECT GROUP_CONCAT(DISTINCT skill_id ORDER BY skill_id DESC) FROM shyfts_skills where shyfts_skills.shyft_id=shyfts.id) as skills,
                    shyfts.section,
                    (SELECT GROUP_CONCAT(DISTINCT attribute_id ORDER BY attribute_id DESC) FROM shyfts_attributes where shyfts_attributes.shyft_id=shyfts.id) as attributes,
                    sd.shyft_type,
                    sdo.dayoff_type as dayoffId,
                    (select code from hr_timesheet_code where id=hcm2.hr_code_id limit 1) as hr_global_code,
                    htc.code as hr_code,
                    dayoff.name AS dayoff_name,
                    dayoff.shortcode AS shortcode,
                    if(d.lang=2, htc.shortcode_nl, htc.shortcode_fr) as hr_shortcode,
                    dayoff.recup AS dayoff_recup,
                    dayoff.real_dayoff AS dayoff_vacation,
                    sdo.paid as dayoff_paid,
                    sdo.fullday as dayoff_fullday,
                    d.country,
                    (select count(*) from publicholidays ph where ph.fulldate = DATE_FORMAT(shyfts.startdate, '%Y-%m-%d') and ph.active=1 and ph.country=d.country) as public_holiday,
                    rpg.hr_max_hours_year, rpg.hr_max_hours_week,
                                (select hours_week from contracts where contracts.ressource_record_id=shyfts.ressource_record_id and contracts.department_id=shyfts.department_id and (contracts.enddate>=shyfts.startdate or contracts.enddate is null) and contracts.active=1 and contracts.archived=0 group by contracts.ressource_record_id order by id) as contract_hours_week,
                                (select days_week from contracts where contracts.ressource_record_id=shyfts.ressource_record_id and contracts.department_id=shyfts.department_id and (contracts.enddate>=shyfts.startdate or contracts.enddate is null) and contracts.active=1 and contracts.archived=0 group by contracts.ressource_record_id order by id) as contract_days_week,
                                rpg.hr_max_hours_month,
                                rpg.hr_max_hours_trimester, rpg.hr_hours_week,
                                rpg.hr_days_week, rpg.hr_low_flexibility, rpg.hr_high_flexibility,
                                rpg.hr_dayoff_year, rpg.lang, i18n.shortcode as languageCode,
                                dpg.default_fulldayhours,
                                sections.section_name as section_name
                from shyfts
                    
                    left join ressources_params_general rpg on rpg.ressource_record_id = shyfts.ressource_record_id and rpg.department_id=shyfts.department_id
                    left join shyfts_dayoff sdo on sdo.shyft_id = shyfts.id
                    left join shyfts_detail sd on sd.shyft_id = shyfts.id
                    left join dayoff on dayoff.id = sdo.dayoff_type
                    left join department d on d.id = shyfts.department_id
                    left join hr_codes_mapping hcm on hcm.dayoff_id=sdo.dayoff_type and hcm.account_id=d.account_id and hcm.active=1 and hcm.hr_partner_id=rpg.hr_partner
                    left join hr_codes_mapping hcm2 on hcm2.dayoff_id=sdo.dayoff_type and hcm2.account_id=d.account_id and hcm2.active=1 and hcm2.hr_partner_id=(select hr_partner_id from hr_partners_account where account_id=shyfts.account_id and active=1 limit 1)
                    left join hr_timesheet_code htc on htc.id=hcm.hr_code_id
                    left join department_params_general dpg on dpg.department_id = shyfts.department_id
                    left join i18n_language i18n on i18n.id=d.lang
                    left join sections on sections.id=shyfts.section
                            where shyfts.active=1 and shyfts.approved=1 and shyfts.ressource_record_id>0 and shyfts.startdate>="2022-01-01 00:00:00" and shyfts.startdate<"2022-01-31 23:59:59" and shyfts.department_id=1439  and (sd.shyft_type=1 or sd.shyft_type=2)
                group by shyfts.id
                order by ressource_record_id, startdate, enddate, department_id

Answer 1

If your both the servers are having the same dataset in same version. Then its different stats on both the servers might be causing slowness. Check cardinality and do analyze on the tables in environment where it is slow probably that might help. Also other possibility is if you have different sets of optimizers enabled/disabled in both the environments. Please check that as well if any optimizer got enabled or disabled accidentally.

Answer 2

The Optimizer keeps improving, but sometimes it fails to improve for some query. I don't have the patience to go through that entire query to see what might have gone wrong. And I would need at least the EXPLAIN to get started.

Anywhere, here are some tips.

• Some of these indexes, if you don't already have them, may help:

    s:  INDEX(account_id)
    s:  INDEX(startdate)
    s:  INDEX(department_id, ressource_record_id, startdate)
    s:  INDEX(department_id, shyft_id, active, freeshift_approved, startdate)
    rpg:  INDEX(ressource_record_id)
    dpc:  INDEX(department_id,  paid_clockin_break)
    shyfts:  INDEX(active, approved, department_id, ressource_record_id, startdate)
    shyfts:  INDEX(account_id)
    shyfts:  INDEX(startdate)
    shyfts:  INDEX(ressource_record_id, department_id, startdate)
    shyfts_skills:  INDEX(shyft_id)
    shyfts_attributes:  INDEX(shyft_id)
    shyfts_pause:  INDEX(paid, shyft_id, duration)
    shyfts_clockin_attributes:  INDEX(clockin_id)
    sd:  INDEX(shyft_type, shyft_id)
    sdo:  INDEX(shyft_id,  dayoff_type, paid, fullday)
    hcm2:  INDEX(hr_code_id)
    hcm2:  INDEX(dayoff_id,  hr_code_id, account_id, active, hr_partner_id)
    hcm:  INDEX(dayoff_id,  account_id, active, hr_partner_id, hr_code_id)
    rpg:  INDEX(user_type)
    ph:  INDEX(fulldate, active, country)
    d:  INDEX(country)
    contracts:  INDEX(ressource_record_id, department_id, enddate, active, archived)
  

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

• Think about some way to store the data so that you avoid OR. (OR does not optimize well.) Perhaps using an enddate in the past instead of NULL.

              and  (contracts.enddate>=shyfts.startdate
                      or  contracts.enddate is null
                   )
  

• If both of these are DATE datatype, there is no need for the un-sargable use of DATE_FORMAT:

       ph.fulldate = DATE_FORMAT(s.startdate, '%Y-%m-%d')
  

• (not a performance issue)

      and  s.startdate>="2022-01-01 00:00:00"
      and  s.startdate <"2022-01-31 23:59:59"
  

-->

      and  s.startdate>="2022-01-01"
      and  s.startdate <"2022-01-01" + INTERVAL 1 MONTH