Counting "tweets", etc

yowmamasita

I am building a social network site for educational purposes and was wondering how should I efficiently count "tweets" of a user (if for ex. on the tweets table he has 100k entries). Should I do it via SQL Count() or should I add a "no. of tweets" field per user to fetch easily and update it when user add/delete a tweet? Or if there are better approach on this, I'd deeply appreciate your input.

How about the case of counting the total no. of characters of these "tweets"? Is SUM(CHAR_LENGTH(arg)) efficient? Or caching an updated value better?

Let's say these values (no. of tweets and no. of characters for all tweets) are always being called because they are displayed publicly on their profiles.

I am just experimenting on algorithms involving large data, so thanks!

thekreek

My 2 cents:

Add the following fields:

• total numbers tweets
• total number of published tweets
• total number of deleted tweets

This way you can save cpu time with less complex querys.

yowmamasita

@thekreek said: My 2 cents:

Add the following fields:

• total numbers tweets
• total number of published tweets
• total number of deleted tweets

This way you can save cpu time with less complex querys.

Thanks! Are there any disadvantages in this approach?

gsrdgrdghd

@yowmamasita said: Are there any disadvantages in this approach?

Yes. I don't think your database will be normalized and you'll have to be careful about keeping everything in sync.

NickM

If this is something that's not going to get a ton of traffic, and you don't have a ton of rows (I'm talking millions of rows here), you might as well just count it each time. If you have proper indexes, doing a a COUNT shouldn't take much resources, and you won't have to worry about the count getting out of sync.