How to iteratively optimize a MySQL query?

Question

I am trying to iteratively optimize a slow MySQL query, which means that I start the query, get the synchronization, configure it, restart it, get the time, etc. The problem is that the time is unsteady and later execution of the request is performed differently compared to earlier versions.

I know to clear the query cache or disable it between execution. I also know that at some level, the OS will affect query performance in ways that MySQL cannot control or understand. But overall, what is the best thing I can do for this kind of iterative query optimization so that I can compare apples to apples?

Answer 1

The best query optimization tool is EXPLAIN . It takes a little time to find out what the output means , but after that you will understand how MySQL (the ugly, broken, reverse) query planner decides in the best way to receive the requested data.

Changes to query parameters can lead to completely different query plans, so this may reflect some of the problems you see.

You might want to consider using the slow query log to capture all the queries that might run at low performance. Perhaps you find that the query in question only falls into the low-performance category when it uses certain parameters?

Answer 2

Create a script that runs the query 1000 times, or any number of iterations causes the results to stabilize.

Then follow your process as described above, but just make sure that you do not rely on one execution, but rather on the number of multiple executions, because you are right, the results will not be stable as the number of rows changes, and your machine does other things.

Also, try using a wide array of inputs for the query, if that makes sense for your use case.