Optimizing query for MySql Database

Question

I need to optimize this query to run with minimum time and retrieve the record.

SELECT DISTINCT cp.sno,cp.case_sno,c.case_no,c.cus_case_no,c.institued_date,
       c.ins_sno,cp.case_stage,c.emp_sno,c.objections,cp.wb_sno,cp.is_decided
FROM cases c,cases_proceedings cp 
WHERE c.sno = cp.case_sno 
      AND cp.is_decided = 1 AND cp.is_current = 1 
      AND c.sno IN(SELECT DISTINCT r.case_sno FROM recordroom r 
                   WHERE c.sno = r.case_sno GROUP BY r.case_sno 
                   ORDER BY r.case_sno) 
GROUP BY cp.case_sno
ORDER BY cp.case_sno ASC

Scenario of the query is that:

   Cases Table is the master table having PK on sno
   cases_proceedings is the child table which repeats Cases' table sno as case_sno
   Recordroom table is also the child table which keeping cases' table sno as case_sno

Problem: The above query takes 17.x seconds to execute which is obviously more than normal time...

Anybody know how to optimize this to get the fruitful result with minimum time?

Answer 1

In first iteration the inner query can be optimized by removing sorting and grouping:

SELECT DISTINCT cp.sno,cp.case_sno,c.case_no,c.cus_case_no,c.institued_date,
       c.ins_sno,cp.case_stage,c.emp_sno,c.objections,cp.wb_sno,cp.is_decided
FROM cases c,cases_proceedings cp 
WHERE c.sno = cp.case_sno 
      AND cp.is_decided = 1 AND cp.is_current = 1 
      AND c.sno IN (SELECT DISTINCT r.case_sno FROM recordroom) 
GROUP BY cp.case_sno
ORDER BY cp.case_sno ASC;

In second iteration we can rewrite the query using joins:

SELECT DISTINCT 
    cp.sno,cp.case_sno,c.case_no,c.cus_case_no,c.institued_date,
    c.ins_sno,cp.case_stage,c.emp_sno,c.objections,cp.wb_sno,cp.is_decided
FROM cases c
JOIN cases_proceedings cp ON c.sno = cp.case_sno 
JOIN recordroom r ON c.sno = r.case_sno
WHERE cp.is_decided = 1 AND cp.is_current = 1 
-- GROUP BY cp.case_sno /*redundant with DISTINCT*/
ORDER BY cp.case_sno ASC;