Mysql Query optimization

Question

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?

Jason Heo · Accepted Answer · 2013-12-01 12:16:46Z

1

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)

edited Dec 1, 2013 at 12:16

answered Dec 1, 2013 at 12:10

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Query optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related