1

I need to display the number of comments a user has post. I can think about two different ways of doing it, and I would like to know which one is better.

METHOD ONE: Each time I need to display the number of comments, query the comments table to select all comments with user_id x, and count the number of results.

METHOD TWO: Add a new column to the user table to store the number of comments a particular user has post. This value will be updated each time the user enters a new comment. This way every time I need to show the number of comments, I just need to query this value in the datbase.

I think the second method is more efficient, but I would like to know other opinions.

Any comment will be appreciated.

Thanks in advance, Sonia

Improve this question
6
  • 2
    You're asking for opinions, which are explicitly off-topic for this site. We are not here to help design your system for you.
    – Marc B
    Commented Jun 9, 2015 at 16:19
  • 1
    Method 1 is clearly the simplest & most robust; a count on an indexed column should be extremely fast, if you run in to issues in the future you can always rethink.
    – Alex K.
    Commented Jun 9, 2015 at 16:19
  • 2
    @MarcB That's not true. I'm not here to design the systems of others for them, but I am here to help them do just that. This question does not really fall in to the 'opinion' category of which you're (rightly) wary. Performance issues aside, one method is clearly preferable to the other.
    – Strawberry
    Commented Jun 9, 2015 at 16:23
  • 1
    stackoverflow.com/questions/6696063/…
    – Jose Manuel Abarca Rodríguez
    Commented Jun 9, 2015 at 16:23
  • 2
    @Sonia MarcB was not rude; just forthright (plus you did indicate that you would appreciate any comment)
    – Strawberry
    Commented Jun 9, 2015 at 16:25

2 Answers 2

Reset to default
1

Well it depends. I suppose you use SQL. Counting is pretty fast of you have correct indexes (eg. SELECT COUNT(1) FROM articles WHERE user_id = ?). If this would be bottleneck than I would consider caching of these results.

Improve this answer
0

At scale, option #2 is the only one that is viable. Counts may eventually be skewed some and you may need to rebuild the stats but this is a relatively low cost compared to trying to count the number of rows matching a secondary index.

Improve this answer

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