This post is part of a series. Read the Intro if you missed it.
As we discussed in the last post, the whole idea of analyzing your database queries is to help speed up your application, reduce database load, and increase your scalability.
The easiest decision, is deciding which queries aren’t worth optimizing.
Try commenting the query out – In a dream world, database queries all take zero time, and cause zero load on the database. Luckily, in the world of testing we can step into the looking glass! Try commenting the query out, even put some static data in its place. This helps you make sure you know how your application would behave if you didn’t do the query at all.
How often is the query run? – Is the query run often enough to cause enough load for others, or to have an impact on a sizable portion of your users? If a query takes 10 minutes but is only run 4 times per day, then you’re really limiting your potential impact of optimization. Instead of optimizing, can these queries be run in the background and save the results? Even if you reduce these queries to 1 second each, the overall change in database load is not going to be significant.
The query is heavily indexed – Many of the queries in our application such as loading account data happen many, many times. However, these queries are also heavily indexed and many of them are in tables that change relatively little. These queries are very fast, return only a few rows, and run very quickly. These queries are also typically not doing any sorting or grouping. These queries are great candidates for caching, but they’re not likely to affect your database performance.
Experiment – You know your application best, so experiment a bit. The most important lesson is to check every step along the way that what you thought would help performance is in fact improving. Even with this warning, large scale applications are complicated and so is their performance. Sometimes you’ll spend time optimizing things and find out they didn’t bear the improvements you wanted. Make sure you learn from these and consider whether your changes are worth the complexity they’re introducing or whether you should just roll them back!
Now that we’ve discussed some quick ways to determine if the queries aren’t worth optimizing. Stay tuned for Part 3.