Dynamic COUNT() Versus Static COUNT() 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.

Published on Feb 24, 2009

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | Optimization | count()

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.

Tutorials

Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.