Q:

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?

A:

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)

