0

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.

1 Answer 1

1

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.

2
  • Consider there is a potential of the table to grow as large as 10,000 of rows, even then, should I consider storing the counts? Nov 28, 2016 at 5:37
  • 1
    The COUNT(*) won't scan 10K rows, only the rows for the user in question.
    – Rick James
    Nov 28, 2016 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.