3

Suppose the memory is enough,

how does MySQL determine whether to fetch from cache or re-query?

1 Answer 1

5

AFAIK mysql automatically expires it is caches when the table changes (inserts, updates, delete's, etc)

mysql usually cache it right away based on the query_cache_limit and size, the default query_cache_limit is 1M so if the individual query goes bigger then that it won't cache unless you change it.

You must make sure that your query are iquals aswell since it takes into consideration case-sensitive queries.

Aside from that you can use the follow command to monitor it:

SHOW STATUS LIKE '%qcache%';

So for example create a table with some entries and run:

SELECT id,name FROM my_table WHERE id > '2'; SHOW STATUS LIKE '%qcache%';

You will noticed that the number of queries cached increased by 1, now run the same selection again and you will see that mysql will be using the cache already and the result will take place a lot faster.

ADDITIONAL INFORMATION

First you must make sure you have query-cache-type = 1 at your my.cnf or my.ini depending on the OS/INSTALLATION.

From mysql you can run the follow command to verify the status of it:

SHOW VARIABLES LIKE '%query_cache%';

When listing the data you will see the query-cache-size, if that is set to 0 it means your query-cache is disabled, this variable determines the memory, in bytes, used for the query cache.

So as you did for the query-cache-type, add the following to your my.cnf or my.ini:

query-cache-size = 40M

2
  • Thanks,but this doesn't answer when mysql expires the cache.
    – db2
    Aug 12, 2010 at 5:59
  • Oh my bad i saw the question as how does MySQL determine whether to fetch from cache or re-query? and forgot about the title of the topic i've updated my answer with what i know about the cache expire bolded on the begin of it.
    – Prix
    Aug 12, 2010 at 6:20

You must log in to answer this question.