Why do people saves count of X in new database's column?

Lets imagine that there are three tables - 'users', 开发者_如何学运维'products' and 'faves'. In user table are stored all users with something like 'id', 'username', 'email'; in 'products' : 'id', 'name', 'price'; in 'faves' : 'id', 'product_id' (ID of product, related with 'products'), 'user_id' (ID of user who added fave, related with 'users'), 'modified_on'. Its dummy example and made only to illustrate what I'm talking about... (:

I often see that there are one more column for 'products' table. Its called 'count_of_faves' or something like that. Why does its needed? I mean, its easily possible to count faves for specified post like on-the-fly, right? Is it somehow related to speed of counting them when there are very many data?

Extra question:

Is there any better, more automatic, way to do +1 and -1 to 'count_of_faves' then each time making new query that updates that row?

---

On a large-scaled system, you can save a lot of processing time by quickly retrieving the count_of_faves rather than having to recalculate the aggregate COUNT() on some rowset every time you need it. If you were, for example, to display the count_of_faves on each page, you would either need to recalculate it each time, or cache it somewhere. You can choose to cache it in your application code, in the session for example, or you can cache it in the database.

Caching it in the database has a few benefits - when creating reports of your data outside your application code, the values are readily available for use.

The operation:

UPDATE table SET count_of_favs = count_of_favs + 1 WHERE id='whatever';

is far less expensive to perform than it is to be recalculating the aggregate COUNT() all the time.

---

It's an optimisation made to reduce the number of COUNTs executed where there may be a large number of relations. To take your example if the number of 'faves' is small the benefit is limited and possibly negative (as the count has to be updated every time a 'fave' is created or destroyed).

But if the count is read far more often than it changes by relations being added / removed then storing the value becomes an effective caching strategy.