sql - 优化更新命令，为每一行聚合整个表

Question

我有 2 个表，company 和 investment，我需要在公司表中有一列，其中包含每家公司的投资数量。

顺便说一下，这是一个 sqlite 数据库。

我尝试了以下查询:

UPDATE company SET numlinks = (SELECT count(*) 
                                   FROM investment 
                                   WHERE investment.company_name = company.name);

我很确定查询是正确的。如果我为一家公司运行它，该行会正确更新。但是鉴于我有超过 300K 行，查询开始运行并且似乎需要一段时间。

当使用 .timer ON 命令为单个公司运行它时，使用的 CPU 约为 0.03(我不确定单位，我猜它以秒为单位)

关于如何使它更快的任何想法？

Answer 1

这是最简单的解决方案。

alter table company
drop column numlinks

数据库规范化的首要原则之一是不存储计算值。当你想显示链接数时，需要的时候再查询。

select company.name, other_stuff, count(*) links
from company join investment on company.name = investment.company_name
group by company.name, other_stuff

顺便说一句，根据您当前的设计，如果两家公司同名，您就会遇到麻烦。这就是名称字段很少用于标识记录的原因。

如果在理解这个答案时遇到困难，我听说过这本书的好东西，即凡人的数据库设计。