Counting items or incrementing a number?

From someone with more experience than myself, would it be a better idea to simply count the number of items in a table (such as counting the number of topics in a category) or to keep a variable that holds that value and just increment and call it (an extra field in the category table)?

Is there a significant difference between the two or is it just very slight, and even if it is slight, would one method still be better than the other? It's not for any one particular project, so please answer generally (if that makes se开发者_如何学JAVAnse) rather than based on something like the number of users.

Thank you.

To get the number of items (rows in a table), you'd use standard SQL and do it on demand

SELECT COUNT(*) FROM MyTable

Note, in case I've missed something, each item (row) in the table has some unique identifier, whether it's a part number, some code, or an auto-increment. So adding a new row could trigger the "auto-increment" of a column.

This is unrelated to "counting rows". Because of DELETEs or ROLLBACK, numbers may not be contiguous.

Trying to maintain row counts separately will end in tears and/or disaster. Trying to use COUNT(*)+1 or MAX(id)+1 to generate a new row identifier is even worse

I think there is some confusion about your question. My interpretation is whether you want to do a select count(*) or a column where you track your actual count.

I would not add such a column, if you don't have reasons to do so. This is premature optimization and you complicate your software design.

Also, you want to avoid having the same information stored in different places. Counting is a trivial task, so you actually duplicating information, which is a bad idea.

I'd go with just counting. If you notice a performance issue, you can consider other options, but as soon as you keep a value that's separate, you have to do some work to make sure it's always correct. Using COUNT() you always get the actual number "straight from the horse's mouth" so to speak.

Basically, don't start optimizing until you have to. If everything works fine and fast using COUNT(), then do that. Otherwise, store the count somewhere, but rather than adding/subtracting to update the stored value, run COUNT() when needed to get the new number of items

In my forum I count the sub-threads in a forum like this:

SELECT COUNT(forumid) AS count FROM forumtable

As long as you're using an identifier that is the same to specify what forum and/or sub-section, and the column has an index key, it's very fast. So there's no reason to add more columns than you need to.