tags:

views:

1809

answers:

6

Hi all

I'm testing the speed of some queries in MySQL. The database is caching these queries making it difficult for me to get reliable results when testing how fast these queries are.

Is there a way to disable caching for a query?

System: MySQL 4 on Linux webhosting, I have access to PHPMyAdmin.

Thanks

+15  A: 

Try using the SQL_NO_CACHE option in your query.

eg.

SELECT SQL_NO_CACHE * FROM TABLE

This will stop MySQL caching the results, however be aware that other OS and disk caches may also impact performance. These are harder to get around.

Jarod Elliott
perfect, thanks!
+4  A: 

You can also run the follow command to reset the query cache.

RESET QUERY CACHE
djt
+1  A: 

If you want to disable the Query cache set the 'query_cache_size' to 0 in your mysql configuration file . If its set 0 mysql wont use the query cache.

Jinesh
+1  A: 

One problem with the

SELECT SQL_NO_CACHE * FROM TABLE

method is that it seems to only prevent the result of your query from being cached. However, if you're querying a database that is actively being used with the query you want to test, then other clients may cache your query, affecting your results. I am continuing to research ways around this, will edit this post if I figure one out.

wbharding
A: 

There used to be a /*!no_query_cache*/ comment you could add to your SELECT queries but I am unable to find it documented on the web anymore. Maybe it's obsolete?

Ztyx
A: 

You can also flush the whole cache of MySQL with

FLUSH QUERY CACHE
hydrarulz