Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
MS SQL Server has some great functions for returning execution plans that you can use for optimising queries, rather than guesswork by running the query over and over. Perhaps MySQL has something similar? –  Mark Henderson Jul 14 '11 at 5:46
    
Is this a production server or a test server just idling? –  Janne Pikkarainen Jul 14 '11 at 6:28
    
It's a test server. –  Yadun Jul 14 '11 at 6:35
    
@Mark it's dev.mysql.com/doc/refman/5.0/en/explain.html EXPLAIN <query>; –  Tom O'Connor Jul 14 '11 at 9:09
    
Nothing about the question just for share. I am just very happy for solve a very slow query. This query is used for livings status of website and base on about 300,000 data. So we just get the latest 1,000 data as the temporary query table with same conditions then we get 100 times speed. So simple so happy. –  Yadun Jul 15 '11 at 9:55

2 Answers 2

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".

share|improve this answer

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.

share|improve this answer
    
Agree. I have consulted many people and always got an advice, that I need a clean and isolated server as my sandbox. –  Yadun Jul 14 '11 at 9:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.