Replace Subquery with JOIN - MYSQL

Question

Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query :

SELECT sa.id AS patAppIdSA,
    sa.rte_id AS patRTEId,
    sa.case_type_id AS patAppCaseId,
    DATE_FORMAT(sa.sa_app_start_date, '%m-%d-%Y') AS saDate,
    DATE_FORMAT(sa.sa_app_starttime, '%h:%i %p') AS saTime,
    CONCAT_WS(', ', usSA.lname, usSA.fname) AS saPhy,
    fac.name AS saFac,
    CONCAT(pd.lname, ', ', pd.fname) AS patName,
    sa.sa_patient_id AS patIdSA,
    insComp.name AS insCompName,
    insComp.claim_type AS insCompType,
    insComp.rte_chk,
    insData.provider AS patInsProvider,
    insData.id AS insRecId,
    insData.policy_number AS insPatPolicyNo,
    insData.ins_caseid AS insPatCaseId,

(SELECT rtme.id
    FROM  real_time_medicare_eligibility rtme
    WHERE rtme.ins_data_id = insData.id
      AND EB_responce != ''
    ORDER BY rtme.request_date_time DESC
    LIMIT 0 , 1) AS rte_master_id,
   (SELECT DATEDIFF('2018-04-13', DATE_FORMAT(rtme.request_date_time, '%Y-%m-%d'))
    FROM real_time_medicare_eligibility rtme
    WHERE rtme.ins_data_id = insData.id
      AND EB_responce != ''
    ORDER BY rtme.request_date_time DESC
    LIMIT 0 , 1) AS rte_passed_days 

FROM schedule_appointments sa
INNER JOIN users usSA 
    ON usSA.id = sa.sa_doctor_id
INNER JOIN facility fac 
    ON fac.id = sa.sa_facility_id
INNER JOIN patient_data pd 
    ON pd.id = sa.sa_patient_id
INNER JOIN insurance_case insCase 
    ON insCase.patient_id = sa.sa_patient_id
INNER JOIN insurance_data insData 
    ON insData.pid = sa.sa_patient_id
    AND insData.ins_caseid = insCase.ins_caseid
INNER JOIN insurance_companies insComp 
    ON insComp.id = insData.provider
WHERE sa.sa_doctor_id IN (280 , 102, 103, 100, 182, 282, 1, 237,
                          96, 105, 106, 107, 306, 303)
    AND sa.sa_facility_id IN (1)
    AND sa.sa_app_start_date = '2018-04-18'
    AND sa.sa_patient_app_status_id NOT IN (201 , 18, 19, 20, 203)
    AND sa.rte_id = '0'
    AND insData.actInsComp = '1'
    AND insData.type = 'primary'
    AND insData.ins_caseid > 0
    AND insData.ins_caseid = sa.case_type_id
    AND insCase.ins_case_type = '1'
ORDER BY sa.sa_app_start_date , sa.sa_app_starttime;

MYSQL version is 5.7.20.

Answer 1

A method to emulate LATERAL joins (called CROSS / OUTER APPLY in some DBMS) I describe in this question: Select ONE most recent post for each author. It is often useful for greatest-n-per-group queries, as yours, and similar problems.

The trick is to rewrite the inline subquery as a join but not as a derived table. The subquery becomes the right part of the ON condition. Since your two subqueries are identical, except for the returned column, one gain is that we now only need one "lateral" join, instead of two subqueries.

Regarding performance, it's not sure that you'll gain significantly, but with proper indexes, this type rewriting is usually faster, especially if the "base" table (insData in this case or rather the product of the joins in the FROM clause) is not big.

Your query will be written (the unchanged parts omitted for clarity):

SELECT sa.id AS patAppIdSA,
    --
    -- several columns omitted
    --
    insData.ins_caseid AS insPatCaseId,

    rtme.id AS rte_master_id,
    DATEDIFF('2018-04-13', DATE_FORMAT(rtme.request_date_time, '%Y-%m-%d'))
            AS rte_passed_days
FROM schedule_appointments sa
    --
    -- several joins omitted
    --
    LEFT JOIN real_time_medicare_eligibility AS rtme
    ON rtme.id =                      -- I assume this is
       (SELECT ri.id                  -- the Primary Key
        FROM  real_time_medicare_eligibility AS ri
        WHERE ri.ins_data_id = insData.id
          AND ri.EB_responce <> ''
        ORDER BY ri.request_date_time DESC
        LIMIT 1)
WHERE sa.sa_doctor_id IN (280 , 102, 103, 100, 182, 282, 1, 237,
                          96, 105, 106, 107, 306, 303)
    --
    -- WHERE predicates omitted
    --
    AND insCase.ins_case_type = '1'
ORDER BY sa.sa_app_start_date, sa.sa_app_starttime ;