Rails/mysql SUM distinct records - optimization
- by pepernik
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 then 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)?
