MySQL Query Cache Per Database / Table
caching, mysql, performance
Solution
Unfortunately, there are very few options that lets you manipulate the MySQL query cache.
The `query_cache_limit` option instructs MySQL to not cache query results larger than a set limit. Reasons why you would want to lower this value:
- some relatively rare queries return large result sets
- most slower queries typically return small result sets
The `SQL_NO_CACHE` keyword, immediately placed after the `SELECT` statement, instructs MySQL to not cache this result.
Conversely, you could set the `query_cache_type` server option to `2` so that only queries using the `SQL_CACHE` keyword are cached.
I would also advise to make sure your query cache is actually fully used. `SHOW STATUS LIKE 'Qcache_free_memory';` gives you this information. If a high proportion of your query cache is free, then it probably means that your data changes too frequently for the results in cache to be reused. It could also mean that most of your queries return results sets larger than `query_cache_limit` (which in turn probably suggests badly designed queries).
There are a few other tips here.
However, you are correctly wondering whether this is worth the hassle., In my opinion the query cache is, at best, a secondary factor for a fast database. Appropriate indexing is the first factor. Moreover, in many cases, your memory would be better used for caching indexes (the most important parameters being the `innodb_buffer_pool_size` for InnoDB tables, or the `key_buffer_size` for MyISAM tables)
Problem
I have one MySQL server running on Linux. It has a few critical apps, and we have set the querycache as high as we could afford. I also have a few non critical databases, (wordpress / etc). Questions: - Is it possible to fine tune query cache on a per database level? - Is it possible to fine tune query cache on a per table basis? - Is it even worth doing? Having a fine tuned query cache on critical tables, will the db still stutter when accessing non important data? Thanks in advance