MySQL Forums
Forum List  »  MyISAM

Re: second execution of the query is faster even the query cache is disabled
Posted by: Rick James
Date: November 09, 2013 04:16PM

Yet another example of how useless the Profiler is.

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.

Options: ReplyQuote

Written By
Re: second execution of the query is faster even the query cache is disabled
November 09, 2013 04:16PM

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.