Query is made to be as fast as possible, using only SQL_NO_CACHE 1 in select and limit 1. The results are pretty curious; on the very first run it takes 0.125 - 0.203 sec. and all next runs take usually less than 0.040 sec. Is it some kind of warm-up, or is it related to building query process?
It is not an issue right now, but I want to know why it is so, and avoid it if possible.
Engine - InnoDB
MySQL version - tested as on 5.6 as 5.7
As another answer highlighted, Query Cache is not the only cache. Its use is not even advisable in most cases - in fact, it was removed in MySQL 8.0. The reason is that it has scalability problems (it's governed by a global lock) and it invalidates data far too frequently to be useful in a normal workload.
But InnoDB buffer pool contains indexes and data accessed frequently. After your first query, some indexes and data are cached, so next time they will be read from memory. Probably some data/index pages are accessed only once per query, in which case you should be able to see a slighter difference between second and third execution (first time these pages are not cached).
How to avoid the difference between query execution times? Well, there is no way to make the query faster the very first time it runs, as it needs to read from disk. But then, if your buffer pool is big enough, your query will always be fast. Keep in mind that a big buffer pool is very important for MySQL performance. The general recommendation is to keep it 75-80% of total memory. But in reality, things are more complex:
Another thing is, sometimes every server is restarted. When it happens, if the buffer pool is simply emptied, your queries will be slower after restart - until hot data are cached again. But you can avoid this by setting:
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
In this way, your buffer pool will be (partially) written to disk on shutdown and reloaded at startup, so your queries should be fast even after a restart.
The query cache is only one of several caches involved in a query. Far more important is the buffer pool, which caches blocks read from the filesystem.
In your first query, the blocks containing the table rows and/or indexes used by the query have to be fetched from the actual disk. After that point they'll be in the buffer cache and subsequent queries will find them there.
I'm going to guess that the MySQL buffer cache is actually a thin layer over the filesystem block cache. If that's the case, and you're running on Linux, you can execute the following command to purge the cache. This will not change the buffer cache itself, but it should force the filesystem to reload the blocks the next time the cache is accessed.
sudo echo 3 > /proc/sys/vm/drop_caches
As with any "sudo" command that you find on the Internet, you should research this one before blindly executing it.
