Recording Number of Items in Meta Table?

Question

I'm working on a small digg-style application that will have anywhere between 20-100 items submitted each day. I need to keep a running track on how many items exist at any particular moment. I know that I don't have many records, but I still don't think performing a count() is the best method since I'll be doing this on almost every page. My idea is to create a table specifically for meta-values, like total_articles. I would then increment that value when a new story is submitted, and decrement the value when a story is deleted.

Is this a common solution to this type of problem, or is there a better way? If this is a standard solution, what types of things should I be mindful of when implementing it as my solution?

Answer 1

Definitely a fairly common type of problem. Here's a couple of things I'd keep in mind.

1. Your database engine - COUNT() doesn't have to be expensive, it just depends on how it's implemented. For example, if you're using MySQL, COUNT() is expensive for an InnoDB table. However, if you use a MyISAM table, the number of records is already stored as meta-data, and a count is extremely inexpensive.

2. On a read-heavy site, your approach of separate meta-data tables works well. However, the task of managing the meta-data adds overhead. You could approach this using database triggers, or extra code in your application, but either way, if it's data you manage, it's going to be a bit of extra work for you.

   On a write-heavy site, the performance hit of updating meta-data on every record write might outweigh the reads of COUNT() (or whatever meta fetching you might be doing).

   Weighing the read/write activity can be prudent. It sounds like you're not expecting high volumes of new records, so storing your own counts probably has the performance upside.