Rails/mysql SUM distinct records - optimization
Posted
by pepernik
on Stack Overflow
See other posts from Stack Overflow
or by pepernik
Published on 2010-04-04T17:56:15Z
Indexed on
2010/04/05
12:13 UTC
Read the original article
Hit count: 260
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)?
© Stack Overflow or respective owner