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.
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:
The code took: 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.
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.
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