Optimise ORDER BY in MYSQL Query

Question

I have the following query which uses order by with limit. It takes 2 min 25 sec to pull around 16k data.I have done PROPER INDEXING too but still PERFORMS SLOW. Same time is taken when only LIMIT 20 is applied too. When ORDER BY is removed, query fetches same data in 17 secs.All the tables are in latin1 charset.Please suggest any possible solutions.

SELECT 
a.customer,
a.division AS division,
a.noitaziraa_id AS noitaziraaId,
DATE_FORMAT(a.request_date, '%m/%d/%Y') AS RequestDate,
a.request_date AS RequestDateSort,
DATE_FORMAT(noita.date_of_birth, '%m/%d/%Y') AS dob,
noita.date_of_birth AS dobSort,
IF(
a.noita_type = 'Noita Stay',
a.length_of_stay,
NULL
) AS requestedDays,
IF(
a.noita_type = 'Noita Stay',
CONCAT_WS(
  ',',
  a.facility_provider_city,
  a.facility_provider_state
),
''
) AS facilityCityState,
IF(
a.noita_type = 'Noita Stay',
IFNULL(
  DATE_FORMAT(aips.admission_date, '%m/%d/%Y'),
  ''
),
''
) AS admitDate,
IF(
a.noita_type = 'Noita Stay',
aips.admission_date,
''
) AS admitDateSort,
IF(
a.noita_type = 'Noita Stay',
IFNULL(
  DATE_FORMAT(
    aipsd.discharge_date,
    '%m/%d/%Y'
  ),
  ''
),
''
 ) AS dischargeDate,
  IF(
a.noita_type = 'Noita Stay',
aipsd.discharge_date,
''
 ) AS dischargeDateSort,
  IF(
a.noita_type = 'Noita Stay',
IFNULL(dl1.`description`, ''),
''
 ) AS dischargeDisposition,
 a.gender,
 a.age,
  a.relationship AS relationship,
  noita.groupid,
  a.request_type AS requestType,
  a.prog_status AS programStatus,
 dl.description AS billingDetails,
 a.referred_to_npi AS NPI,
 a.program AS program,
 CASE
  WHEN a.status = 'OPEN' 
  THEN DATEDIFF(NOW(), a.auth_request_date) 
  ELSE 0 
  END AS 'daysSinceRequest',
  a.first_name AS firstName,
 a.last_name AS lastName,
dl2.description AS levelOfUrgency,
 a.member_id AS memberId,
a.created_full_name AS createdFullName,
CONCAT_WS(
',',
COALESCE(a.assigned_to, NULL),
COALESCE(
  a.auth_review_assigned_user_name,
  NULL
),
COALESCE(
  a.auth_con_review_assigned_user_name,
  NULL
),
COALESCE(a.assigned_queue, NULL),
COALESCE(
  a.auth_review_assigned_queue_name,
  NULL
 ),
 COALESCE(
  a.auth_con_review_assigned_queue_name,
  NULL
 )
 ) AS assignedTo,
a.status,
DATE_FORMAT(a.opened_date, '%m/%d/%Y') AS openDate,
 a.opened_date AS openDateSort,
 DATE_FORMAT(a.closed_date, '%m/%d/%Y') AS closedDate,
 a.closed_date AS closedDateSort,
 a.noita_type AS authType,
 a.facility_provider AS facilityProvider,
 a.length_of_stay AS lengthOfStay,
 DATE_FORMAT(a.requested_from, '%m/%d/%Y') AS authFromDate,
 a.requested_from AS authFromDateSort,
 DATE_FORMAT(a.requested_through, '%m/%d/%Y') AS authToDate,
 a.requested_through AS authToDateSort,
 a.pended,
  a.diagnosis AS diagnosis,
 a.diagnosis_desc AS diagDesc,
 a.auth,
a.denied,
a.excluded,
a.admit_type AS admitType,
a.service_type AS serviceType,
a.proc,
a.proc_desc AS procDesc,
a.plan 
FROM
main_table a 
INNER JOIN noitaciary noita 
ON noita.id = a.noitaciary_id 
INNER JOIN usermanagement.`user` usr 
ON a.created_by = usr.id 
AND 
CASE
  WHEN CONCAT(usr.firstname, ' ', usr.lastname) IN ('a', 'b *', 'c', 
   'd', 'd', 'f') 
  THEN 1 = 1 
  ELSE (
    COALESCE(usr.`employer`, '') NOT IN ('r', 's')
  ) 
  END 
    LEFT JOIN noitaziraa_ips AS aips 
    ON aips.noitaziraa_id = a.auth_id 
  LEFT JOIN db1.`noitaziraa_history` ah 
   ON ah.noitaziraa_id = a.noitaziraa_id 
 LEFT JOIN noitaziraa_ips_discharge AS aipsd 
  ON aipsd.noitaziraa_ips_id = aips.id 
 LEFT JOIN noitaziraa_phr AS aphr 
  ON aphr.noitaziraa_id = a.auth_id 
  LEFT JOIN noitaziraa_sp AS asp 
  ON asp.noitaziraa_id = a.auth_id 
  LEFT JOIN noitaziraa_decisions AS auth_dec 
 ON a.auth_id = auth_dec.noitaziraa_id 
 LEFT JOIN mytable AS aa 
 ON a.noitaziraa_id = aa.noitaziraa_id 
LEFT JOIN db1.dw_lookup dl 
 ON auth_dec.details = dl.code 
LEFT JOIN db1.`dw_lookup` dl1 
ON dl1.`code` = aipsd.`discharge_diposition` 
 AND dl1.`data_type` = 'dataTypeName' 
 LEFT JOIN db1.dw_lookup dl2 
 ON aa.level_of_urgency = dl2.code 
 AND dl2.data_type = 'dataTypeName1' 
LEFT JOIN 
    (SELECT 
     * 
   FROM
  (SELECT 
    hh.noitaziraa_id,
    hh.`status` 
  FROM
    db1.`noitaziraa_history` hh,
    main_table a 
  WHERE hh.noitaziraa_id = a.noitaziraa_id 
    AND hh.client = 'certainValue' 
    AND DATE(hh.last_updated) < '2017-12-01 00:00:00' 
  GROUP BY hh.`last_updated` 
  ORDER BY hh.last_updated DESC) tmp 
GROUP BY noitaziraa_id) AS tps 
ON tps.noitaziraa_id = a.noitaziraa_id 
  WHERE a.customer LIKE 'certainValue%' 
   AND a.status <> 'VOID' 

  AND DATE(auth_dec.requested_through) >= '2017-12-01 00:00:00' 
AND DATE(auth_dec.requested_through) <= '2017-12-05 00:00:00' 
AND DATE(a.opened_date) <= '2017-12-05 00:00:00' 
 AND (
 (
    DATE(ah.last_updated) BETWEEN '2017-12-01 00:00:00' 
  AND '2017-12-05 00:00:00' 
   AND ah.status IN (
    'OPEN',
    'CLOSED',
    'REOPENED',
    'CANCELED'
     )
    ) || (
    tps.noitaziraa_id = a.noitaziraa_id 
     AND tps.status IN (
    'OPEN',
    'CLOSED',
    'REOPENED',
    'CANCELED'
  )
   )
  ) 
  GROUP BY a.auth_id 
  ORDER BY groupid ASC 
  LIMIT 0, 20

noitaziraa_history table contains large number of rows and it must be left joined to meet my requirements which is taking lot of time.

Using EXPLAIN gives the following:

RamaKrishna · Accepted Answer

As per the question, you have applied proper indexing too. I suppose you are correct on that. Then, Please avoid the use of LEFT JOIN with the noitaziraa_history table as it is also mentioned by @Rick James. If it is possible, make sure to load the data in this table so that all the noitaziraa_id in your primary table be in history table too.Now, you can apply INNER JOIN instead of LEFT JOIN you are using and see the result. Also, refactor everything that seems inappropriate or of no use at present following Rick James' suggestion. I am sure INNER JOIN will reduce the time taken by LEFT JOIN if you have multiple rows of data for a same row in your primary table. One thing more, please apply any conditions filter if you can during the JOIN with the nnoitaziraa_history table like you have done in the subquery below:

    INNER JOIN db1.`noitaziraa_history` ah 
    ON ah.noitaziraa_id = a.noitaziraa_id AND  hh.client = 'certainValue' AND  DATE(hh.last_updated) <  '2017-12-01 00:00:00'

Please update if it works for you :) Thanks!

Optimise ORDER BY in MYSQL Query

Answers (2)

Related Questions