Reducing Database Connections with Subqueries
In my past post, I talked about how to improve performance of your SQL queries by using a simple WHERE clause. In other instances when you’re trying to improve performance you may take each query and find that there’s not much you can do to improve the individual queries, however, you may be able to combine multiple queries by using subqueries and thus reducing the number of database connections required by your page, and significantly improving your page performance.
This is a relatively basic optimization tip, but I’ve seen many beginners looping through each row of a database resultset and then executing a query on each of those rows. This means if you have 100 rows in that first resultset , you’ll be executing 100 database queries on that page alone. You won’t notice a bid difference in performance if you don’t have many rows in your first resultset, or if you don’t have a lot of traffic to the site to use up those database connections, but as soon as your first resultset grows, or the traffic to your site increases, you’ll find your page performance slowing to a crawl.
To use an example, maybe you have an online photo gallery. You have a home page that lists each photo album in a table along with the first photo in each album and the first comment of that photo. I’ve seen it done a lot where novice developers will simply write an SQL query to grab a list of photo albums from that database. Then loop through the resultset and use the album id to grab a photo from each of the albums in the loop. If you start out with 4 or 5 albums, it won’t be a big deal, but when you get 20 or 30 or even 100 you’ll see some considerable slowdown.
You might think to do something like this, to grab the name and id of each album in your database.
SELECT PA.photoAlbumId, PA.name FROM photoAlbum PA
Then you loop through that resultset and for each photoAlbumId you run this SQL
SELECT P.photoId, P.name FROM photo P WHERE P.photoAlbumId=$row->photoAlbumId LIMIT 1
The problem here is that if you have 100 albums, you’re creating 100 connections per page. The process grows exponentially as you try to add comments or other one-to-many attributes
Here’s an SQL query, using subqueries to eliminate the need to do a looping database connection, it grabs the photo album name, the source for a photo in the album, and a comment from that first photo in the album.
SELECT PA.albumId, PA.name, P.imageSrc, C.comment
FROM photoAlbum PA
LEFT JOIN (SELECT P.photoId, P.imageSrc, P.photoAlbumId FROM photo P LIMIT 1) P ON P.photoAlbumId=PA.photoAlbumId
LEFT JOIN (SELECT C.comment, C.photoId FROM comment C LIMIT 1) C ON C.photoId=P.photoId
This will grab all the information in one query that you can loop through once, and reduce the number of SQL connections substantially from the previous, beginner’s method.
