When the slowest is not the problem.
One of the most common mistakes in a query optimization task is not selecting the queries you really should optimize. Usually all the efforts are on the slowest executions without considering it’s frequency and the circumstances when it occurred.
Considering the time when the slowest executions happened is important for two main reasons: it may be part of a maintenance operation that is executed during a planned maintenance window without compromising the performance of your application; or it can be a victim instead of the root cause of the real problem.
The other important aspect to examine is the frequency which the slowest queries are being executed. A slow but not frequent query can have a minor impact to the database overall performance when compared to a faster but very frequent query.
A recent case from one of our customers illustrates this scenario. They had some very slow queries but the slowest queries were not the root cause of their performance issues.
Comparing the two queries below, the first query has an Average Execution Time of 3m 39s 785ms while the second query takes on average 421ms to be executed.
If we had considered only the Average Execution Time, the first query would be our choice for the optimization. The point is that this query is executed only once a day and it is not during "business hours”. So optimizing it wouldn't bring any benefit to the performance of the application at all.
In the other hand, even being really faster, optimizing the second query brought a powerful impact to the performance of the application as it was executed 61669 times during the whole day.
“With the help of Nazar we were able to identify bottlenecks in database queries very easily. Once identified, we deployed compound indices on a frequent visited table and were able to save up to 50% of our database load.” Jan Nietfeld- CTO, at Recorrido.cl
