Optimise Mysql Query

Question

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:

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

Hi @Peter is there any logic for using NULL at ORDER BY clause? — Rahul Biswas, Aug 09 '21 at 05:33
Hi @Peter Joining condition with string makes the query slower because string contains lowercase, uppercase or empty space in beginning or ending string. It is better to used integer field to better searching. Index is also efficient at integer data type instead of string data type. — Rahul Biswas, Aug 09 '21 at 06:26
@RahulBiswas Not necessarily, I could take out the ORDER BY NULL, but it wont make any difference, do you have further recommendations? — Peter, Aug 09 '21 at 09:13

Rahul Biswas · Accepted Answer · 2021-08-10T09:52:19.007

2

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;

edited Aug 10 '21 at 09:52

answered Aug 09 '21 at 09:27

Rahul Biswas

3,207
2
10
20

1

Hi @Peter please check this query and let me know if you've any query regard this. – Rahul Biswas Aug 09 '21 at 09:28
1

Hi @Peter are you handling all data of this three tables? Because no searching criteria is used here. It'll slow when tables contains huge data. Use id instead of name at JOIN condition. – Rahul Biswas Aug 09 '21 at 09:35
1

@Peter any feedback about my query. – Rahul Biswas Aug 09 '21 at 11:12
thankfully, your query works fine but you have ommited this part of it: `SUM(b.approved) AS summedLoans,`. Can you update please? Thanks a lot. – Peter Aug 10 '21 at 09:40
1

Hi @Peter please check now. Sorry I forgot to pick that column. Now I've added this. – Rahul Biswas Aug 10 '21 at 09:53
Thanks a lot Rahul Biswas, do you mind helping to optimise more queries? – Peter Aug 10 '21 at 09:56
1

Pardon me, I forgot to say that after using your query, execution time is now 0.0898 seconds – Peter Aug 10 '21 at 09:57
Welcome @Peter. I'll try my level best to help you for optimizing query. – Rahul Biswas Aug 10 '21 at 09:58
1

kindly upvote this question so that other developers can easily find it, thank you – Peter Aug 10 '21 at 10:04

score 0 · Answer 2 · answered Aug 09 '21 at 06:08

0

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

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

answered Aug 09 '21 at 06:08

vinieth

1,204
3
16
34

Optimise Mysql Query

2 Answers2