CODEWITHSUNDEEP
mysql
seoppc
seoppc

Reputation: 2824

Mysql Query optimization

I have following MySQL tables...

users table

| id  | account_number | referred_by |
+-----+----------------+-------------+
| 44  | 100            | 200         |
+-----+----------------+-------------+
| 182 | 200            | 500         |
+-----+----------------+-------------+
| 184 | 300            | 500         |
+-----+----------------+-------------+

Earnings table

| id  | account_number | earnings    |
+-----+----------------+-------------+
| 44  | 100            | 50.00       |
+-----+----------------+-------------+
| 182 | 200            | 20.00       |
+-----+----------------+-------------+
| 184 | 300            | 10.00       |
+-----+----------------+-------------+

I am trying to fetch the sum of total_earnings earned by referred members, for example. If total_earnings of members would be 30.00 referred_by account number '500' and I have tried following MySQL query...

SELECT SUM(earnings) 
FROM earnings 
WHERE account_number IN 
    (SELECT account_number FROM users WHERE referred_by = 500);

This is however too slow. How can I optimize this query?

Upvotes: 0

Views: 92

Answers (1)

Jason Heo
Jason Heo

Reputation: 10246

would try this?

SELECT SUM(earnings.earnings)
FROM earnings INNER JOIN users USING(account_number)
WHERE users.referred_by = 500;

users table should have INDEX(referred_by, account_number), earnings should have INDEX(account_number)

Upvotes: 1

Related Questions