title: Testing performance of queries in mysql
body: <p>I am trying to setup a script that would test performance of queries on a development mysql server. Here are more details:</p>
  
  <ul>
  <li>I have root access</li>
  <li>I am the only user accessing the server</li>
  <li>Mostly interested in InnoDB performance</li>
  <li>The queries I am optimizing are mostly search queries (<code>SELECT ... LIKE '%xy%'</code>)</li>
  </ul>
  
  <p>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.</p>
  
  <p>Till now I have been using <a href="http://dev.mysql.com/doc/refman/5.1/en/select.html" rel="noreferrer">SQL_NO_CACHE</a>, 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.</p>
  
  <p>If someone can explain this behaviour in full detail I might stick to using <code>SQL_NO_CACHE</code>; I do believe that it might be due to file system cache and/or caching of indexes used to execute the query, as <a href="http://www.mysqlperformanceblog.com/2007/09/12/query-profiling-with-mysql-bypassing-caches/" rel="noreferrer">this</a> post explains. It is not clear to me when Buffer Pool and Key Buffer get invalidated or how they might interfere with testing.</p>
  
  <p>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?</p>