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?
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?