Re: second execution of the query is faster even the query cache is disabled
Posted by:
Rick James
Date: November 09, 2013 04:16PM
<opinion>
Yet another example of how useless the Profiler is.
</opinion>
The "Query cache" is one flavor of caching, but not the only...
MySQL (MyISAM and InnoDB) reads data and indexes from disk, then caches what it reads. (MyISAM relies the OS for "data" caching.) The performance difference is very likely to be due to this flavor of caching. The first invocation is slow due to fetching blocks from disk; the second (and subsequent) invocation can take advantage of the cached blocks.
Rule of Thumb: First invocation is 10x slower. Your example is more like 30x. That's a bit on the high side, but still supports my block-caching theory.
Some more observations:
preparing -- CPU only, so no diff between the two.
Sorting result -- possibly done entirely in RAM, hence fast and reasonably consistent.
logging slow query -- perhaps you have long_query_time=10, so the second invocation was not logged?
statistics (optimizer) -- probably had to read from the .MYI file; then it was kept in some cache.
open_tables -- costly the first time; already open the second time. See table_open_cache. This is yet another cache.
Subject
Views
Written By
Posted
7470
November 08, 2013 01:21AM
Re: second execution of the query is faster even the query cache is disabled
3242
November 09, 2013 04:16PM
2587
November 11, 2013 03:02AM
2699
November 11, 2013 11:21PM
2218
November 13, 2013 12:32AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.