CODEWITHSUNDEEP
mysqloptimizationcomposite-index
Peter
Peter

Reputation: 62

Optimise Mysql Query

I would like to optimise the query I have below:

SELECT
                      a.id, a.pay_point_name, a.pay_point_location,
                      COUNT(b.id) AS countedCustomers,
                      SUM(b.approved) AS summedLoans,
                      SUM(c.deduction) AS summedDeductions 
                  FROM
                      pay_tbl a 
                  LEFT JOIN
                      customer_tbl b 
                          ON b.employer = a.pay_point_name 
                  LEFT JOIN
                      loans_tbl c 
                          ON c.paypoint = a.pay_point_name 
                  GROUP BY
                      a.pay_point_name 
                  ORDER BY
                      NULL

Current Execution time: 161.2s

EXPLAIN statement gives me the table below: EXPLAIN statement gives me this table

I would like to know how best to optimise this query and reduce execution time.

Upvotes: 1

Views: 61

Answers (2)

Rahul Biswas
Rahul Biswas

Reputation: 3467

Please check this one where I've used subquery. If this works better create view with this query.

-- MySQL
SELECT t.id, t.pay_point_name
     , t.pay_point_location
     , COALESCE(t.countedCustomers, 0) countedCustomers
     , COALESCE(t.summedLoans, 0) summedLoans
     , COALESCE(p.summedDeductions, 0) summedDeductions
FROM (SELECT a.id, a.pay_point_name
           , MAX(a.pay_point_location) pay_point_location
           , COUNT(b.id) AS countedCustomers
           , SUM(b.approved) AS summedLoans
      FROM pay_tbl a 
      LEFT JOIN customer_tbl b 
             ON b.employer = a.pay_point_name
      GROUP BY a.id, a.pay_point_name) t
LEFT JOIN (SELECT paypoint
                , SUM(deduction) AS summedDeductions
           FROM loans_tbl
           GROUP BY paypoint) p
       ON t.pay_point_name = p.paypoint
ORDER BY t.id;

Upvotes: 2

vinieth
vinieth

Reputation: 1314

Suppose if you have to repeatedly execute the same query frequently. You can use MySql views.

https://www.mysqltutorial.org/mysql-views-tutorial.aspx

Upvotes: 0

Related Questions