Allows suppose you will find three tables -
'users', 'products' and 'faves'. In user table are saved all customers with something similar to
'id', 'username', 'email' in
'id', 'name', 'price'; in 'faves' :
'product_id' (ID of product, related to
'user_id' (ID of user who added fave, related to 'users'),
'modified_on'. Its dummy example making simply to illustrate what I am speaking about... (:
I frequently observe that you will find yet another column for 'products' table. Its known as 'count_of_faves' or something like that like this. How come its needed? I am talking about, its easily easy to count faves for specified publish like on-the-fly, right? Could it be in some way associated with speed of counting them when you will find lots of data?
Can there be much better, more automatic, method of doing
-1 to 'count_of_faves' then every time making new query that updates that row?
On the large-scaly system, it can save you lots of processing time by rapidly locating the
count_of_faves instead of needing to recalculate the aggregate
COUNT() on some rowset any time you require it. Should you be, for instance, to show the
count_of_faves on each page, you'd either have to recalculate it every time, or cache it somewhere. You are able to decide to cache it inside your application code, within the session for instance, or cache it within the database.
Caching it within the database includes a couple of benefits - when designing reviews of the data outdoors the application code, the values can easily be bought to be used.
UPDATE table SET count_of_favs = count_of_favs + 1 WHERE id='whatever';
is way less costly to do than to become recalculating the aggregate
This is an optimisation designed to reduce the amount of COUNTs performed high might be a lot of relations. To consider your example if the amount of 'faves' is small the advantage is restricted and perhaps negative (because the count needs to be up-to-date whenever a 'fave' is produced or destroyed).
But when the count is read much more frequently of computer changes by relations being added / removed then storing the worthiness becomes a highly effective caching strategy.