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

Related posts about mysql

Related posts about mysql-query