MySQL single query benchmarking strategies

Question

I have a slow MySQL query in my application that I need to re-write. The problem is, it's only slow on my production server and only when it's not cached. The first time I run it, it will take 12 seconds, then any time after that it'll be 500 milliseconds.

Is there an easy way to test this query without it hitting the query cache so I can see the results of my refactoring?

Answer 1

MySQL supports to prevent caching single queries. Try

SELECT SQL_NO_CACHE field_a, field_b FROM table;

alternatively you can diasble the query cache for the current session:

SET SESSION query_cache_type = OFF;

See http://dev.mysql.com/doc/refman/5.1/en/query-cache.html