Dynamic COUNT() Versus Static COUNT()

I’ll continue today with another good database programming article.  If you missed yesterdays, you may view it here.

Today’s article will discuss when to use a static COUNT() versus a dynamic COUNT().  On a regular basis we are tasked with creating a message center or a photo album and we need to display a count of new messages or pictures in the album.

The obvious solution is to do a simple query: SELECT COUNT(*) FROM pictures WHERE album_id = 1 and display the result beside our album name.  Now, let’s assume that we allow multiple albums our actual query would be closer to this: SELECT albums.*, COUNT(pictures.*) as picture_count FROM albums INNER JOIN pictures ON albums.id = pictures.album_id WHERE albums.id = 1.  This will of course work, but let me show you a better, more practical approach that will improve performance significantly on large databases.

Let’s think about this and come up with the best approach.  The first thing we need to do is ask ourselves, “How often will the count of pictures change?”  The answer should be pretty simple: when we add a new one and when we delete an existing picture.

With that knowledge, let’s make a change to our albums table and add a new integer field called “picture_count”.  Now, let’s update our add script to increase picture_count by 1 after we have saved our new picture for the album.  Do the same for the delete, but decrease it instead.

Now, we can update our original query from:
SELECT albums.*, COUNT(*) as picture_count FROM albums INNER JOIN pictures ON albums.id = pictures.album_id WHERE albums.id = 1

to:
SELECT * FROM albums WHERE id = 1

Our albums listing page doesn’t need any changes because we are simply replacing the dynamic picture_count field with the new static picture_count field.  If your web site is relatively small you may not see immediate improvements, however, if you have a larger web site with 100s of albums and 1000s of pictures there should be a considerable difference on each page load.

One last bonus tip.  A better practice than increasing picture_count by 1 or decreasing it by 1 would be to actually perform: SELECT COUNT(*) FROM pictures WHERE album_id = 1 AND do an update on the albums table with this value.  This requires an extra query, however, it provides more accuracy.

Hopefully the lesson you will learn from today’s article is that it is important to think about when should I use dynamic data versus static data.