Rails/mysql SUM 不同记录 - 优化
嘿。您将如何优化此 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;
我将此示例用作 Ruby on Rails 项目的一部分。请注意，我的嵌套 SQL (tmp) 可以有超过 1000 万条记录。如果性能更好，您可以将其拆分为更多 SQL。 我是否应该添加任何索引以使其更快（我在 ID 上有它）？
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)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您应该对此 SQL 代码中涉及的所有外键都有索引，例如：
client_groups.client_id、clients.country_id、clients_groups.group_id
但是，主要是
当您有那么多的条目时，使用 SUM() 或 COUNT() 等 SQL 函数可能会降低性能
因此，我建议您保留成本缓存，并根据影响成本的每笔交易进行更新。
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.