CODEWITHSUNDEEP
mysqloptimization
contool
contool

Reputation: 1074

How can I optimise mySQL to use JOINs instead of nested IN queries?

I have a query which combines a user's balance at a number of locations and uses a nested subquery to combine data from the customer_balance table and the merchant_groups table. There is a second piece of data required from the customer_balance table that is unique to each merchant.

I'd like to optimise my query to return a sum and a unique value i.e. the order of results is important.

For instance, there may be three merchants in a merchant_group:

id           |       group_id          |        group_member_id
1                       12                           36
2                       12                           70
3                       12                           106

The user may have a balance at 2 locations but not all in the customer_balance table:

id           |   group_member_id  |   user_id   | balance     |   personal_note     
1                      36               420         1.00         "Likes chocolate"
2                      70               420        20.00          null

Notice there isn't a 3rd row in the balance table.

What I'd like to end up with is the ability to pull the sum of the balance as well as the most appropriate personal_note.

So far I have this working in all situations with the following query:

SELECT sum(c.cash_balance) as cash_balance,n.customer_note FROM customer_balance AS c
    LEFT JOIN (SELECT customer_note, user_id FROM customer_balance 
            WHERE user_id = 420 AND group_member_id = 36) AS n on c.user_id = n.user_id
    WHERE c.user_id = 420 AND c.group_id IN (SELECT group_member_id FROM merchant_group WHERE group_id = 12)

I can change out the group_member_id appropriately and I will always get the combined balance as expected and the appropriate note. i.e. what I'm looking for is: balance: 21.00 customer_note: "Likes Chocolate" OR null (depending on the group_member_id)

Is it possible to optimise this query without using resource heavy nested queries e.g. using a JOIN? (or some other method).

I have tried a number of options, but cannot get it working in all situations. The following is the closest I have gotten, except this doesn't return the correct note:

SELECT sum(cb.balance), cb.personal_note FROM customer_balance AS cb
LEFT JOIN merchant_group AS mg on mg.group_member_id = cb.group_member_id 
WHERE cb.user_id = 420 && mg.group_id = 12 
ORDER BY (mg.group_member_id = 106)

I also tried another option (but since lost the query) that works, but not when the group_member_id = 106 - because there was no record in one table (but this is a valid use case that I'd like to cater for).

Thanks!

Upvotes: 1

Views: 27

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

This should be equivalent but without subselect 

SELECT 
      sum(c.cash_balance) as cash_balance
    , n.customer_note 
FROM customer_balance AS c
LEFT JOIN customer_balance as n on ( c.user_id = n.user_id AND n.group_member_id = 36  AND n.user_id = 420 )
INNER JOIN merchant_group as mg on (  c.group_id = mg.group_member_id AND mg.group_id = 12)
WHERE c.user_id = 420

Upvotes: 1

Related Questions