Rails/mysql SUM distinct records - optimization

Hey. How would you optimize this SQL

SELECT SUM(tmp.cost) FROM (
   SELECT DISTINCT clients.id as client, countries.credits_cost AS cost
   FROM countries
   INNER JOIN clients ON clients.country_id = countries.id
   INNER JOIN clients_groups ON clients_groups.client_id=clients.id
   WHERE clients_groups.group_id IN (1,2,3,4,5,6,7,8,9)
   GROUP BY clients.id
) AS tmp;

I'm using this example as part of my Ruby on Rails project. Note that my nested SQL (tmp) can have more t开发者_Go百科hen 10 milion records. You can split that in more SQLs if the performance is better. Should I add any indexes to make it quicker (i have it on IDs)?

You should have indexes on all the foreign keys involved in this SQL code such as: clients_groups.client_id, clients.country_id, clients_groups.group_id

However, mainly When you have that number of entries, Using SQL functions such as SUM() or COUNT() can be performance killing So I suggest you keep a cache of the cost and update it with each transaction affecting the cost.