CODEWITHSUNDEEP
mysqlperformance
Pepper
Pepper

Reputation: 3022

MySQL single query benchmarking strategies

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?

Upvotes: 5

Views: 1528

Answers (2)

David M
David M

Reputation: 4376

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.

Upvotes: 2

johannes
johannes

Reputation: 15989

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

Upvotes: 7

Related Questions