I am tuning SQL query time for my slow query but every time I get different times so I can't evaluate my optimize solution.
For example, I run query select * from abc where abc.status in (x,y,z) and abc.scale>123; three times, and I get result time like 5s, 11s, 9s.
I have turned off the query cache. How can I get a clean sandbox for tuning my slow query?
Most likely, the server is doing other different things in the background. MySQL queries are very much affected by IO, and disturbances in the form of other processes performing IO work on the machine can change query times significantly.
Make sure you're running this on an isolated server if you want repeatable benchmarks.
The other alternative to having a pristine and untainted environment is to run your query repeatedly (as in, hundreds of times) and do some statistical analysis of your results. The key is to isolate any conflating variables (running the tests on the same machine, with the same scaffolding code, and without any biases in the other tasks running (difficult, I know). You can then make probabalistic conclusions as to whether a particular query is better than some other (although quantifying the exact improvement is frought with peril).
Or you can just use EXPLAIN <query>, which will show you a query plan and give you a very good idea of which of two queries is "better".
