Basic SQL Query Optimization with WHERE Clauses
Optimizing SQL queries is something that most beginning and novice web developers either aren’t experienced enough to worry about, or they just simply don’t consider it. I’ve worked on a lot of sites built by other people where the most simple SELECT statements are used, even if it means using 3, 4, or more database queries when only 1 is really necessary. The problem is that most of the beginners simply get the site to “work” and if everything is showing up on the page properly, then they consider the task complete. SQL can be very easy to learn and if your sites are relatively low-traffic, then it really doesn’t make a difference if you’re querying the database more than you need to - or if your queries aren’t optimized like they should be - since the site won’t get overloaded with 500 visits per day. However, as soon as you start to get a lot of traffic to your site, your page can start to really slow down. That’s when you need to either upgrade your server or hosting plan, or you can improve on your SQL.
I recently worked on an auction site that was very database-heavy. Because of the amount of traffic and number of connections needed per page, the database began to get overwhelmed. One of the first steps to improve performance of your site if it is getting bogged down by traffic is to take a look at your database connections and query optimization – you may also want to upgrade server hardware, which I would always recommend in complement to optimizing code.
Optimize SQL Queries With Where Clauses
The first query I noticed was a query on the home page of the auction that listed the most recent bids in the auction. The query grabbed the item details, and highest bidding information from the database for the 20 most recent bids in the site. I grabbed the twenty most recent records from the bid table and then join a bunch of other tables to get the item, company, and user details. Here it is:
SELECT I.itemId IitemId, max(B.amount) bidAmount, I.name, max(B.bidDate) lastBid, I.value, Ca.name CaName, Ca.categoryId, I.pictureThumbnail, Co.logoThumbnail, Co.name CoName, Co.companyId CoCompanyId, B.username
FROM item I
LEFT JOIN category Ca ON Ca.categoryId=I.categoryId
LEFT JOIN company Co ON Co.companyId=I.companyId
LEFT JOIN (SELECT itemId, amount, bidDate, username FROM bid B LEFT JOIN account A ON A.accountId=B.accountId ORDER BY amount DESC) B ON B.itemId=I.itemId
GROUP BY I.itemId
ORDER BY B.bidDate DESC
LIMIT 20
When I grabbed the database and code from the server and set it up on my laptop for testing, I found that running this query through mysql took 4.78 seconds! That meant every time the page would load the page would take 4.78 seconds to grab the data it needed from the database, among other database queries included on the page. Keep in mind, that MySQL will cache query results based on the settings in your my.cnf file, so if the results from this query didn’t change, it would be lightning quick for the next person to load the page, however, any time a new bid was placed, these results would change and the cached query would not be used. During peak levels of the auction we had close to a bid every 15-30 seconds so this was definitely slowing down the page and being the home page, a lot of users noticed. There was no problem at all when the auction started, but because the bid table had close to 20000 bids by the end of the auction it really slowed down.
What did I do to speed it up? All I added was a simple WHERE clause – WHERE B.bidDate>’2007-11-20 12:00:20′ – dynamically generating the date stamp to 1 hour before the current time.
SELECT I.itemId IitemId, max(B.amount) bidAmount, I.name, max(B.bidDate) lastBid, I.value, Ca.name CaName, Ca.categoryId, I.pictureThumbnail, Co.logoThumbnail, Co.name CoName, Co.companyId CoCompanyId, B.username
FROM item I
LEFT JOIN category Ca ON Ca.categoryId=I.categoryId
LEFT JOIN company Co ON Co.companyId=I.companyId
LEFT JOIN (SELECT itemId, amount, bidDate, username FROM bid B LEFT JOIN account A ON A.accountId=B.accountId ORDER BY amount DESC) B ON B.itemId=I.itemId
WHERE B.bidDate>’2007-11-20 12:00:20′
GROUP BY I.itemId
ORDER BY B.bidDate DESC
LIMIT 20
Because I knew there would be bids every few seconds, I could rely on their being plenty of bids each hour to fill up the 20 most recently bid on items. The difference in query time? Adding the WHERE clause reduced the query speed to 0.45 seconds. That’s a huge increase in performance – 4.78 seconds to 0.45 seconds.
I wanted to show the increase in performance by optimizing the SQL query here, but you could also take a look at what indexes you have setup on your database tables to help further improve performance of your database.
