0

I want to know whether saving row count in a table is better than counting it each time of the proccess.

Quick Example : A visitor goes to Group Clan, the page displays clan information and Members who have joined the group,Should the page look for all the users who joined the clan and count them, or just display the number of members already saved in table ?

I think the first one is not possible to get manipulated with but IT MIGHT cost performance

Your Ideas ?

Improve this question
1
  • I dont think this will ever be a performance problem for you database. these kind of iterations your asking for can be solved easily by the client displaying the list, since it has to iterate through the list to display it, you can easily add rowcount info to that.
    – Carlo Kuip
    Nov 1, 2011 at 8:51

4 Answers 4

Reset to default
1

you are optimizing the wrong thing :), your group page is not very likely to change so what you really want to do is cache the complete html-output and return your group page from the cache which leads to zero db round-trips and therefor much more performance gain then an optimized db-query

Improve this answer
0

You may use a view to return the count. Depending on your DB, the view results may be cached till the participating tables change, resulting in efficiency. But make sure the results are cached and that indices are maintained on views too. Storing a count means updating it everytime you write to the table(s), too. Check out this thread.

Improve this answer
2
  • from the mysqlperformanceblog.com they say views should be used carefully in rare cases since MySQL still needs alot to get it optimized, so i drop the views thing, what about a comparison between above ?
    – SAFAD
    Nov 1, 2011 at 8:32
  • I'd prefer calling count everytime, though there is nothing wrong in storing it, if you do it atomically, as James mentions.
    – yati sagade
    Nov 1, 2011 at 10:10
0

Saving the row count will be "faster" but it may cause other problems. You need to ensure the update is completely atomic and not part of a bigger unit of work, otherwise, you will be plagued by deadlocks and timeouts when your site gets even moderately busy.

By "completely atomic" I mean you need to "BEGIN;" immediately followed by the " UPDATE YOURTABLE SET CNT = CNT + 1;" immediately followed by "COMMIT;"

Improve this answer
1
  • Yes thats what I was referring to, so I rather use count for now...
    – SAFAD
    Nov 1, 2011 at 8:53
0

I suggest the following: - Don't display the data if it is not accurate. - If you are worried about performance, display accurate information only based on request - There are ways to get accurate counts cheaply for example consider either of these approaches:

1 - Count function can run against the database meta data and does not count rows (depending on your database)

2 - Use the concept of Materialized View (as in Oracle) (called Indexed View in SQL Server). While MySQL does not support this directly, it may be simulated - See this: MySQL-Simulate Materialized Views

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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