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()
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.
Jamie Munro is the author of ASP.NET MVC 5 with Bootstrap and Knockout.js,
Knockout.js: Building Dynamic Client-Side Web Applications,
20 Recipes for Programming MVC 3,
and 20 Recipes for Programming PhoneGap. He has been developing websites and web applications for over 20 years. Jamie began his writing career in 2009. As the success of Jamie's blog grew, he turned his writing passion to books about web development in hopes that his many years of experience could be passed on to his readers. 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.
Related Posts
Tutorials
Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.