COUNT（*）vs手动跟踪计数器？

I have a table with approx. 70000 entries. It holds information about brands, models and categories of goods. The user can query them using any combination of those, and the displayed counter of goods matching the criteria has to be updated according to his selection.

我有一张大约一张桌子。 70000个条目。它包含有关商品的品牌,型号和类别的信息。用户可以使用它们的任意组合来查询它们,并且必须根据他的选择更新所显示的符合标准的商品的计数器。

I have it done using a query like

我使用像这样的查询完成它

SELECT model,COUNT(*) AS count FROM table$model_where 
          GROUP BY model
          ORDER BY count DESC

where $model_where depends on what the other conditions were. But my boss asked me to redo these queries into using a special counter table, because he believes they are slowing the whole process down, but a benchmark I put suggests otherwise, sample output:

其中$ model_where取决于其他条件。但我的老板让我重新使用一个特殊的计数器表来查询这些查询,因为他认为他们正在减慢整个过程,但我提出的基准测试表明,样本输出:

The code took: 0 wallclock secs (0.02 usr + 0.00 sys = 0.02 CPU)

代码采用:0 wallclock secs(0.02 usr + 0.00 sys = 0.02 CPU)

and it measures the whole routine from the start and until the data is send to the user, you can see it's really fast.

它从一开始就测量整个例程,直到数据发送给用户,你可以看到它真的很快。

I have done some research on this matter, but I still haven't seen a definitive answer as to when to use COUNT(*) vs counter tables. Who is right? I'm not persuaded we actually need manual tracking of this, but maybe I just know little.

我已就此问题做了一些研究,但我还没有看到何时使用COUNT(*)vs counter table的明确答案。谁是对的?我不相信我们实际上需要手动跟踪这个,但也许我只是知之甚少。

1 个解决方案

#1

Depending on your specific case, this might, or might not be a case of premature optimization.

根据您的具体情况,这可能是,也可能不是过早优化的情况。

If next week you'll have 100x bigger tables, it might not be the case, but otherwise it is.

如果下周你会有100倍大表,可能不是这样,但不然如此。

Also, your boss should take into consideration that you and everybody else will have to make sure that counters are updated whenever an INSERT or DELETE happens on the counted records. There are frameworks which do that automatically (ruby on rails's ActiveRecord comes to mind), but if you're not using one of them, there are about a gazillion ways you can end up with wrong counters in the DB

此外,您的老板应该考虑到您和其他所有人必须确保在计数记录上发生INSERT或DELETE时更新计数器。有一些框架可以自动执行(ruby on rails的ActiveRecord可以想到),但是如果你没有使用其中一个,那么你可以在数据库中找到错误的计数器。

#1