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

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

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.

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.

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.

You must change SQL string. Because SQL string is a cache key. For example, add a timestamp to a SQL comment.

Function for PHP:

function db_RunSQL($SQL, $NoCacheMode=false)
{
$SQL = (($NoCacheMode) ? '/*'.time().'*/ ' : '') . $SQL;
return mysqli_query(db_SavedConnect(), $SQL);
}