0

I have a query to show payment data paid by grouped total Employee (1,2,3) (their size [how much number of users are in total Employee)) and the total amount.

But My query took 30-35 seconds

result screenshot is attached too.

Actually instead of add filters (having totalEmployee in (1,2,3,4,5) in inner query, I want to manage in only one call to load all the data.

SELECT  
    tmp.total_emp AS totalEmployee,
    COUNT( DISTINCT outerCompany.co_id ) AS companySize,
    SUM( pay_amount ) AS totalAmount
FROM
    company outerCompany
    INNER JOIN 
    ( 
    SELECT innerCompany.co_id, COUNT(*) AS total_emp FROM company innerCompany 
    LEFT JOIN users ON innerCompany.co_id = user_company_id GROUP BY innerCompany.co_id 
    ) tmp ON outerCompany.co_id = tmp.co_id
    LEFT JOIN payments ON outerCompany.co_id = pay_co_id 
GROUP BY
    totalEmployee
ORDER BY
    totalEmployee ASC;

Result enter image description here

Explain enter image description here

I've tried many things but I'm far away from optimization. 1- only holding users query inside subQuery instead of innercompany/users. 2. I've also exclude outer company and only picks tmp result.

Last rows of data screenshot

enter image description here

Any help will be appreciated. thank you.

6
  • Please change some names; I am having trouble understanding the goal. "companySize" -> numberOfCompanies"?; "user" -> "employee"? And, as a further sanity check, please show us the last few lines of the output (with the biggest companies).
    – Rick James
    Nov 13, 2022 at 1:46
  • For example, companies of 8 employees average 2.7 million dollars (or whatever the unit is). Yet for 1 employee it is more like 15K.
    – Rick James
    Nov 13, 2022 at 1:50
  • Thank you for reply. companySize -> is number of companies. Employee -> is like sub_user/multiple user belongs to one company. Nov 13, 2022 at 2:07
  • @RickJames new screenshot is attached with the last rows. I've also added inner query result in the same screenshot. Nov 13, 2022 at 2:32
  • Please provide SHOW CREATE TABLE for each of the 3 tables.
    – Rick James
    Nov 13, 2022 at 5:48

1 Answer 1

1

This may help.

  • Switch LEFT JOIN to subquery
  • Simply derived table (and move LEFT out)
  • Suggest indexes

revised query

SELECT  counts.total_emp AS totalEmployee,
        COUNT( DISTINCT oC.co_id ) AS companySize,
        ( SELECT SUM(p.pay_amount) 
               FROM payments AS p
               WHERE p.pay_co_id = oC.co_id
        ) AS totalAmount
    FROM  company AS oC
    LEFT JOIN  
        ( SELECT  u.user_company_id, COUNT(*) AS total_emp
            FROM  users AS u
            GROUP BY  u.user_company_id
        ) AS counts  ON oC.co_id = counts.user_company_id
    GROUP BY  counts.total_emp
    ORDER BY  counts.total_emp ASC;

Plus indexes:

p:  INDEX(pay_co_id,  pay_amount)
u:  INDEX(user_company_id)
3
  • actually in left join Inside. I previously put same users query but that's not worked too. Let me again share most of Important things. All have indexes. I can share in a while in link. 1- Users have 55K records 2- Company have 40k records, 3- Payments have 6300k records so The previous query shows explain 38k records from whole table (from above image) i.stack.imgur.com/8nZDL.png Nov 13, 2022 at 7:00
  • I'm just try to think either does this type of records took 30 seconds is a valid time. I think I'm doing some mistake ( I've also one alternate solution to manage by divide totalEmployee in chunks like this Inside left join subquery having COUNT() in (1,2,3)*. This type will increase work but reduced some seconds and load data in 15 seconds. e.x: i.stack.imgur.com/WEzJi.png Nov 13, 2022 at 7:50
  • 1
    Thank you. (pay_co_id, pay_amount) by adding only this index helps me. The Page is super fast. It loads the data in 7-8 seconds. thank you so much. Previously pay_co_id indexed but I added pay_amount separately. but adding composite It resolved my problem. Nov 13, 2022 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.