Question 20602 in Mysql Question posted in
The official MySQL documentation can be found here.

How to reduce query time in mysql?

I need to reduce query time from 1.3sec to 0.1sec.

if I don’t compare sum(amount) with sum(pd.amount_take) my query time is 0.03sec

My Code sql:

SELECT p.po_id, 
date_format(p.po_date,'%d-%m-%Y') AS po_date,
p.branch_id,
b.fullname as branch_send,
p.branch_id2,
bb.fullname as branch_recieve

FROM tbl_po_branch p
                                
LEFT JOIN tbl_branch b ON b.branch_id=p.branch_id
LEFT JOIN tbl_branch bb ON bb.branch_id=p.branch_id2
LEFT JOIN tbl_users u ON p.user_id=u.user_id

WHERE b.owner_id= 1
AND p.status = 1 
AND (SELECT sum(amount) FROM tbl_po_branch_detail WHERE PO_id=p.PO_id
     AND branch_id=p.branch_id)
> (SELECT sum(pd.amount_take) FROM tbl_po_detail pd 
LEFT JOIN tbl_po po ON pd.po_id=po.po_id AND pd.branch_id=po.branch_id
WHERE po.po_id2=p.po_id
AND po.branch_id2=p.branch_id
AND po.branch_id=p.branch_id2
AND po.status='2')
Group By p.PO_id,p.branch_id,p.branch_id2
ORDER BY p.po_date DESC,b.fullname
Tags:

2

Answers


  1. 0

    i optimised it a bit.

    SELECT p.po_id, 
       DATE_FORMAT(p.po_date,'%d-%m-%Y') AS po_date,
       p.branch_id,
       b.fullname as branch_send,
       p.branch_id2,
       bb.fullname as branch_recieve
FROM tbl_po_branch p
LEFT JOIN tbl_branch b ON b.branch_id=p.branch_id
LEFT JOIN tbl_branch bb ON bb.branch_id=p.branch_id2
LEFT JOIN tbl_users u ON p.user_id=u.user_id
WHERE b.owner_id= 1
  AND p.status = 1 
  AND (SELECT SUM(pd.amount_take) 
       FROM tbl_po_detail pd 
       JOIN tbl_po po ON pd.po_id=po.po_id 
         AND pd.branch_id=po.branch_id
       WHERE po.po_id2=p.po_id
         AND po.branch_id2=p.branch_id
         AND po.branch_id=p.branch_id2
         AND po.status='2') 
      < (SELECT SUM(amount) 
         FROM tbl_po_branch_detail 
         WHERE PO_id=p.PO_id AND branch_id=p.branch_id
         GROUP BY PO_id, branch_id)
GROUP BY p.PO_id,p.branch_id,p.branch_id2
ORDER BY p.po_date DESC,b.fullname
    Login or Signup to reply.
  2. 0

    These could be useful

    • We could drop the tbl_users table since it is not used
    • Move the sub-query in the where into joins since joins are better in performance than sub-queries
    SELECT 
    p.po_id, 
    date_format(p.po_date,'%d-%m-%Y') AS po_date,
    p.branch_id,
    b.fullname as branch_send,
    p.branch_id2,
    bb.fullname as branch_recieve,
    SUM(pod.amount) as amount,
    SUM(CASE WHEN po.status = '2' THEN pd.amount_take ELSE 0 END) as amount_take

FROM tbl_po_branch p
LEFT JOIN tbl_branch b ON b.branch_id = p.branch_id
LEFT JOIN tbl_branch bb ON bb.branch_id = p.branch_id2
LEFT JOIN tbl_po_branch_detail pod ON pod.PO_id = p.PO_id AND pod.branch_id=p.branch_id
LEFT JOIN tbl_po po ON po.po_id2=p.po_id AND po.branch_id2=p.branch_id AND po.branch_id=p.branch_id2
LEFT JOIN tbl_po_detail pd ON pd.po_id=po.po_id AND pd.branch_id=po.branch_id

WHERE b.owner_id= 1 AND p.status = 1
Group By p.po_id, p.po_date, p.branch_id,b.fullname, p.branch_id2, bb.fullname
HAVING amount > amount_take
ORDER BY p.po_date DESC,b.fullname
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search