Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Don't use SELECT COUNT(id). Use SELECT COUNT(*) instead. With no hints. MySQL optimizer has some blind spots. –  ypercube Jun 24 '12 at 9:20
1  
And I hope you have an index on jusid. –  ypercube Jun 24 '12 at 9:22
    
It is indexed indeed. –  john.locke Jun 25 '12 at 14:24
2  
Is there a requirement that the count must always be correct? Or do you have some flexibility to return approximate results? Does it matter if the count eventually matches reality or can it remain approximate forever? For example, if all comments on a YouTube video were halted and there were actually 1 million comments while the counter said 995,867 comments, no one would probably care about a discrepancy of 4000-some comments. Other systems will have other sorts of requirements for accuracy and consistency. –  Justin Cave Oct 31 '12 at 0:46

2 Answers 2

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

share|improve this answer

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".

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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