Counter Cache

Counter Cache (or Counter Cache Columns) is the term coined when adding a column to data tables in order to keeps track of aggregate data in an application. The term “cache” is used because the application has no need to perform costly count queries. It can simply read the value of the “count column”. This practice is less costly when compared to performing separate database queries. The worst case scenario, where the application has database queries within a loop is avoided too.

Counter Caching – An Example

The simplest example of counter caching is a “posts” and “comments” model, where the application requires dictates that a “popular posts” page is required. The page lists the most commented posts. Each post has many comments.

Adding counter caching in this instance is a case of inserting a column in the “posts” table titled “count_comments”. Whenever a new comment is posted (and approved) the associated comment figure would be incremented. In MySQL:

UPDATE `posts` SET `count_comments` = `count_comments` + 1 WHERE `post_id` = :post:id LIMIT 1

Depending on the application’s business logic further processing may be required. When a comment is deleted, or marked as inactive, the count field would need to be decremented. E.g. (for a single comment that is removed for a particular post_id):

UPDATE `posts` SET `count_comments` = `count_comments` - 1 WHERE `post_id` = :post:id LIMIT 1

The latter varies from site to site – WordPress for instance, allows a user to remove multiple comments in a single bulk action, so something more complicated than the latter is needed. However, it is much less costly and better practice, to do such processing at this stage. E.g. when a comment is approved, deleted or has the status changed.

It is perfectly acceptable to utilise multiple “counter cache” fields in a single table. Using the latter example, it may be relevant to add a “count_edited” column that keeps track of the total number of times a post was edited – saving a count query from the “posts_edit” table.

That’s it, the application is now faster and more efficient, with minimal effort.

Further Counter Cache Use Cases

What follows are a few further samples of counter caching, to illustrate:

• On an ecommerce website, storing the times a product has been successfully ordered in the main “products” table
• On an accounting system, storing a count of the invoices for a particular customer on the “customers” table.
• On a Twitter style messaging application, adding multiple counter cache fields on the main “messages” table for “count_messages”, “count_sent_messages”, “count_drafts” etc.
• On an ecommerce sites that has a “order_header” and “order_items” table – add a column on the “order_header” table titled “order_total” (type: double) – that stores the order total based upon the data in the “order_items” table. This single counter cache field (that doesn’t always have to be an integer…) allows for much less complex SQL when creating reports. For instance, to get aggregate totals for the entire store and individual users, simple MySQL “SUM” queries can be performed.
• On a eBaY style application add four counter cache fields on the “users” table: “count_sales”, “sum_sales”, “count_active_listings”, “count_unread_messages”
• On a blogging system, add a field titled “post_count” to the main category look up table, to store the number of active posts within that query.

There are countless more examples, but the post illusrates how useful counter caching can be in your application. Have you utilised counter caching? Please share you experiences and thoughts here!