计算项目或递增数字？

Question

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 sense) rather than based on something like the number of users.

Thank you.

Answer 1

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