MySQL - force not to use cache for testing speed of query

Question

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

User · Answer

You can also run the follow command to reset the query cache   RESET QUERY CACHE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    See http   dev mysql com doc refman 5 1 en query-cache html

User · Answer

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

User · Answer

You can also run the follow command to reset the query cache   RESET QUERY CACHE

User · Answer

You can also run the follow command to reset the query cache   RESET QUERY CACHE

User · Answer

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

User · Answer

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

User · Answer

Another alternative that only affects the current connection   SET SESSION query cache type 0

User · Answer

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

User · Answer

I d Use the following   SHOW VARIABLES LIKE  query cache type   SET SESSION query cache type   OFF  SHOW VARIABLES LIKE  query cache type

User · Answer

I d Use the following   SHOW VARIABLES LIKE  query cache type   SET SESSION query cache type   OFF  SHOW VARIABLES LIKE  query cache type

User · Answer

You can also run the follow command to reset the query cache   RESET QUERY CACHE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Another alternative that only affects the current connection   SET SESSION query cache type 0

User · Answer

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    See http   dev mysql com doc refman 5 1 en query-cache html

User · Answer

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

User · Answer

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

[mysql] MySQL - force not to use cache for testing speed of query

Examples related to mysql