SELECT T1.PATIENT_ID,T1.PATIENT_PAID AS VP_PAT,
T2.PAID AS LD_PAT,T1.STATUS
FROM (
( SELECT PROCEDURE_ID, PATIENT_PAID, PATIENT_ID, STATUS
FROM VISIT_PROCEDURE
WHERE CLINIC_ID = 97846
) AS T1
LEFT JOIN
( SELECT PROCEDURE_ID, SUM(AMOUNT) AS PAID
FROM LEDGER
WHERE TYPE IN(5,8)
AND CURR_PARTY = 4
AND CLINIC_ID = 97846
GROUP BY PROCEDURE_ID
) AS T2 ON T1.PROCEDURE_ID = T2.PROCEDURE_ID
)
WHERE ROUND(T1.PATIENT_PAID,1) <> ROUND(T2.PAID, 1)
* If a subquery is doing something to diminish the number of rows (GROUP BY, LIMIT, DISTINCT, etc), keep it as a subquery. (You have a GROUP BY on the second subquery)
* Otherwise turn it into a JOIN, but make sure there is a good index. VISIT_PROCEDURE needs INDEX(CLINIC_ID, PROCEDURE_ID).
* Check remaining subqueries for having optimal indexes. LEDGER needs INDEX(CLINIC_ID, CURR_PARTY, TYPE).
* The subqueries will be performed first, and create temp tables with no indexes. In your case (fortunately) there is only one subquery.
* You said "LEFT" JOIN. Was that deliberate, or accidental? It may be throwing a monkey wrench into optimizing, forcing the JOIN to do table scans of the unindexed subquery. Please run EXPLAIN on this to see which table it starts with:
SELECT T1.PATIENT_ID,
T1.PATIENT_PAID AS VP_PAT,
T2.PAID AS LD_PAT,
T1.STATUS
FROM VISIT_PROCEDURE T1
LEFT JOIN
( SELECT PROCEDURE_ID, SUM(AMOUNT) AS PAID
FROM LEDGER
WHERE TYPE IN(5,8)
AND CURR_PARTY = 4
AND CLINIC_ID = 97846
GROUP BY PROCEDURE_ID
) AS T2 ON T1.PROCEDURE_ID = T2.PROCEDURE_ID
WHERE ROUND(T1.PATIENT_PAID,1) <> ROUND(T2.PAID, 1)
AND T1.CLINIC_ID = 97846
If that does not perform well, come back and I will provide you with a messier (but faster) solution.