MySQL caching queries even when caching is disabled?

Question

MySQL

There is a monstrous request. For the first time, it works for quite a long time (about 20-30 seconds). In subsequent times, its execution time is significantly reduced (up to 1-2 seconds).
It is logical to assume that this is a cache, but even when using MYSQL_NO_CACHE and setting query_cache_type to OFF, everything repeats.

What could it be and how to get rid of it?

(Disable cache is necessary in order to see optimization results)

Answer 1

This is not necessarily a cache.
First, when executing a query, MySQL loads all the necessary indexes into memory. If you run the query again immediately afterwards, the indexes are already in memory, and this reduces the query execution time.
Second, MySQL also temporarily caches the query execution plan .

how to get rid of it?

I don't think it's worth (or even possible) getting rid of. No harm, one big benefit :)

Answer 2

sql no cache disables only query caching. For MyISAM, as a rule, there is also a file cache, the key_buffer_size parameter. For InnoDB - innodb_buffer_pool_size

Answer 3

If the result was taken from the cache, it would not take 1-2 seconds, but 1-100 ms. Most likely, just pieces of files from the disk end up in the OS cache and are taken from memory when requested again.
If you work under Linux, the OS cache (for the purity of the experiment) can be reset by writing one somewhere in / proc (google), mysql internal buffers are reset by restarting the daemon.
Those. do service mysql stop, reset the OS cache, service mysql start and execute the query.

Answer 4

I once asked this question, and it seems like I found the answer that the built-in cache does not turn off

Answer 5

Without optimization, a monstroid query works monstrously even with a cache. If everything is in order with the cache, then the request is also ok.
MySQL does not cache sub-queries. You can try nesting your query inside a regular select and time it.