MYSQL Query Cache

Using the mysql query cache can enable the website to grab the queries from a cache rather than having to query the database over and over again. Grabbing the data can work for ‘SELECT’ statements. To enable the query cache, the administrator needs root access. Root access can be aquired with Putty or some other SSH tool.

To see what variables are present in the mysql query cache;
mysql> show variables like ‘query%’;
Look next to query_cache_size and see its value. The value 0 means it is not enabled. A value like 2,000,000 is 2 mb.

To set the query cache to 2 mb;
mysql> set global query_cache_size=20000000;

To see info about the query cache like the amount of free memory and number of queries
mysql> show status like ‘qc%’;

To dump the cache is as easy as setting the global query_cache_size to 0 then setting it to a new value.

Depending upon the default settings, the query cache may be enabled or disabled. To see if query_cache_type is enabled,
mysql> show variables like ‘query%’;

The 0 value is enabled. To enable it,
mysql> set global query_cache_type=1;
or
mysql> set global query_cache_type=2;

If the number does not change, it could be that you logged in with an updated administrator password, rather than the mysql password. To fix this, login to the server with SSH, then, type
$ mysql -u root

December 29, 2010 by kent