Mysql – Store row count or use query to calculate on Primary Key

database-designMySQLnormalization

I am curious to know if I should store the row count for an instance number of friends of a user into a separate table or if I should use a query like

SELECT COUNT(user_id) FROM user_friend WHERE user_id = 20;

where user_id is AUTO_INCREMENTED PRIMARY_KEY

Is querying the COUNT each time slower than storing the row account in a separate table?

In my opinion, I shouldn't store the row counts separately as this will lead to Denormalization and also some other factors like having to update the row count again and again and fetching the records by joining the tables unnecessarily.

Best Answer

Redundant information in a database is a no-no.

If you have an index (such as the PRIMARY KEY) on user_id and if there is not thousands of rows for a user, the computing it on the fly is the way to go.

Related Question