I am trying to setup a script that would test performance of queries on a development mysql server. Here are more details:

• I have root access
• I am the only user accessing the server
• Mostly interested in InnoDB performance
• The queries I am optimizing are mostly search queries (SELECT ... LIKE '%xy%')

What I want to do is to create reliable testing environment for measuring the speed of a single query, free from dependencies on other variables.

Till now I have been using SQL_NO_CACHE, but sometimes the results of such tests also show caching behaviour - taking much longer to execute on the first run and taking less time on subsequent runs.

If someone can explain this behaviour in full detail I might stick to using SQL_NO_CACHE; I do believe that it might be due to file system cache and/or caching of indexes used to execute the query, as this post explains. It is not clear to me when Buffer Pool and Key Buffer get invalidated or how they might interfere with testing.

So, short of restarting mysql server, how would you recommend to setup an environment that would be reliable in determining if one query performs better then the other?