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
This question is related to
mysql
Whilst some of the answers are good, there is a major caveat.
The mysql queries may be prevented from being cached, but it won't prevent your underlying O.S caching disk accesses into memory. This can be a major slowdown for some queries especially if they need to pull data from spinning disks.
So whilst it's good to use the methods above, I would also try and test with a different set of data/range each time, that's likely not been pulled from disk into disk/memory cache.
Another alternative that only affects the current connection:
SET SESSION query_cache_type=0;
Try using the SQL_NO_CACHE (MySQL 5.7) option in your query. (MySQL 5.6 users click HERE )
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.
You can also run the follow command to reset the query cache.
RESET QUERY CACHE
There is also configuration option: query_cache_size=0
To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead. If you build MySQL from source, query cache capabilities can be excluded from the server entirely by invoking configure with the --without-query-cache option.
I'd Use the following:
SHOW VARIABLES LIKE 'query_cache_type';
SET SESSION query_cache_type = OFF;
SHOW VARIABLES LIKE 'query_cache_type';
Try using the SQL_NO_CACHE (MySQL 5.7) option in your query. (MySQL 5.6 users click HERE )
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.
Whilst some of the answers are good, there is a major caveat.
The mysql queries may be prevented from being cached, but it won't prevent your underlying O.S caching disk accesses into memory. This can be a major slowdown for some queries especially if they need to pull data from spinning disks.
So whilst it's good to use the methods above, I would also try and test with a different set of data/range each time, that's likely not been pulled from disk into disk/memory cache.
Using a user-defined variable within a query makes the query resuts uncacheable. I found it a much better indicator than using SQL_NO_CACHE. But you should put the variable in a place where the variable setting would not seriously affect the performance:
SELECT t.*
FROM thetable t, (SELECT @a:=NULL) as init;
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.
You can also run the follow command to reset the query cache.
RESET QUERY CACHE
Any reference to current date/time will disable the query cache for that selection:
SELECT *,NOW() FROM TABLE
See "Prerequisites and Notes for MySQL Query Cache Use" @ http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
Another alternative that only affects the current connection:
SET SESSION query_cache_type=0;
Any reference to current date/time will disable the query cache for that selection:
SELECT *,NOW() FROM TABLE
See "Prerequisites and Notes for MySQL Query Cache Use" @ http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
Try using the SQL_NO_CACHE (MySQL 5.7) option in your query. (MySQL 5.6 users click HERE )
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.
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.
You can also run the follow command to reset the query cache.
RESET QUERY CACHE
Using a user-defined variable within a query makes the query resuts uncacheable. I found it a much better indicator than using SQL_NO_CACHE. But you should put the variable in a place where the variable setting would not seriously affect the performance:
SELECT t.*
FROM thetable t, (SELECT @a:=NULL) as init;
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.
There is also configuration option: query_cache_size=0
To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead. If you build MySQL from source, query cache capabilities can be excluded from the server entirely by invoking configure with the --without-query-cache option.
Try using the SQL_NO_CACHE (MySQL 5.7) option in your query. (MySQL 5.6 users click HERE )
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.
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.
I'd Use the following:
SHOW VARIABLES LIKE 'query_cache_type';
SET SESSION query_cache_type = OFF;
SHOW VARIABLES LIKE 'query_cache_type';
Source: Stackoverflow.com