Why is selecting the right queries to optimize so important? There are several reasons.
Penny-Wise, Pound-Foolish
Look at the forest, not just the trees, and optimize globally, not locally. If you’re optimizing a query that never causes a user-visible problem, doesn’t impact other queries, and doesn’t significantly load servers, you might be “optimizing” things that don’t matter, spending more money than you save. Your time has value, too! Keep in mind, too, that optimizing a query that generates only 1% of the database’s overall load will not be a significant benefit to the bottom line.
Whack-A-Mole Queries
It’s very common to find a slow query in a log file, try re-executing it and then find that it is fast. It was slow at some point in the past, but why? It can be hard to tell the difference between bad queries, cache misses, victims of bad server performance overall, and victims of other bad queries. This is especially true if you don’t have a good way to examine what was happening at the time of a bad query. And if you can’t measure the effects of any changes you make, you might just spend your time going in circles.
With this in mind, select queries that are:
Major contributors
As a rule of thumb, queries that contribute more than about 5% of the total execution time (service demand) are worth examining individually to see if they can be improved. The exact number is up to your judgment, but that’s our suggestion.
Consistently slow
Queries that have high latency generally cause user-facing performance problems if they are executed with any frequency.
Occasionally slow, and important
Finding queries that are sometimes slow is an important step towards finding small server-wide stalls that may worsen in the future, or may indicate long-tail latency problems or occasionally broken functionality. Many applications have a “celebrity” edge-case, named after the outlying effects of highly popular celebrity accounts on social media, which have orders of magnitude more followers and activity than ordinary accounts. Such edge cases can easily fall through the cracks of average-case analysis.
Queries with Red Flags
Queries that cause errors or warnings, don’t use indexes, and the like may be good to clean up, even if it’s purely to avoid “broken window syndrome” and keep your workflow clean and manageable.
In short, if you aren't selective in which queries you choose, you may end up overburdening and slowing down your team - essentially defeating the purpose of optimization in the first place!
