MySQL Single Query Benchmarking Strategies

Question

Hello,

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 anytime 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?

Thanks!

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

Answer 2

To add to johannes's good answer, what I do is

RESET QUERY CACHE;

This has the slight added advantage of not requiring any changes to either the statements I'm executing or the connection.

A trivial thing to do is to alter the statement you're executing somehow, such as put a random number in a comment, because a queries are located in the cache only if they are byte-identical to some previous query.