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?

Slava Rozhnev · Accepted Answer · 2020-03-17 09:47:55Z

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;

answered Mar 17, 2020 at 9:47

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Watson Over a year ago

The first solution is amazing, it reduced the time from 17 seconds to 0.17 seconds with same result, I have only one question, removing WHERE c.sno = r.case_sno GROUP BY r.case_sno ORDER BY r.case_sno part of clauses from the query, i think it will compare all the case_sno's one by one?

Slava Rozhnev Over a year ago

@John Watson The query works different. You can use EXPLAIN SELECT ... for look query plane

Rick James Over a year ago

And give dp this composite index: (is_current, is_decided, case_sno)

Collectives™ on Stack Overflow

Optimizing query for MySql Database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related