Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?

Question

In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records:

SELECT COUNT(id) shareLikeNo FROM user_share_like WHERE jusid=$jusid

Or update a field like comment_no in shares(posts) table? I have fields like shares_no,comments_no,likes_no. Everytime a user click like button the likes_no field will be incremented and other fields as I explained.
This way I have to update every record each time a user post a comment or shares a post or likes a post.
Should I use SELECT COUNT(id) FROM tblname and so on for getting the record count or just updating a record.
There will about 10 to 20 posts each second. Site's traffic will rose.
Which approach should I use? Which one is more expensive?

EDIT:
I've used use index(PRIMARY) as below to speed things up:

SELECT COUNT(id) shareLikeNo FROM user_share_like USE INDEX(PRIMARY) WHERE jusid=$jusid

Another approach is:

SELECT SUM(1) shareLikeNo FROM user_share_like WHERE jusid=$jusid

Is this a better approach?

Answer 1

1. How frequent do you need to perform this query?

   If it is very rarely, then, perhaps it doesn't matter. And you could do "SELECT COUNT(1) FROM ...", do no need to use "SELECT COUNT(id)". SELECT COUNT(id) is not necessary in your case. Please refer here for more about when to use SELECT COUNT(1) etc.

2. As pointed out by Justin Cave above, does it need to be accurate all the time?

   If you need it to be accurate, you might have to suffer the cost of the query.

   If not, perhaps you could consider caching the value and only perform the query once every time period (once per 5 seconds?)

Answer 2

There are many subtle things that could be involved, these will help direct the discussion:

SHOW CREATE TABLE shareLikeNo;

SHOW TABLE STATUS LIKE 'shareLikeNo';

SHOW VARIABLES LIKE '%buffer%';

If your traffic gets too busy, you may have abandon the notion of real-time updating of "likes".