To speed up query, enable the MySQL query cache, before that you need to set few variables in mysql configuration file ( my.cnf or my.ini)
The first you make sure that your installation of MySQL actually has query caching support available.
mysql> SHOW VARIABLES LIKE 'have_query_cache';
mysql> show variables like 'query%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +------------------------------+---------+ 7 rows in set (0.00 sec) mysql>
query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.
query_cache_type – This value must be ON or 1 for query caching to be enabled by default.
query_cache_limit – This is the maximum size query (in bytes) that will be cached.
If the query_cache_size value is set to 0 or you just want to change it, you’ll need to run the following command, keeping in mind that the value is in bytes.
For instance, if you wanted to allocate 9 MB to the cache we’d use 1024 * 1024 * 9 = 9437184 as the value.
SET GLOBAL query_cache_size = 9437184;
Similarly, the other options can be set with the same syntax:
SET GLOBAL query_cache_limit = 1048576;
SET GLOBAL query_cache_type = 1;
You can use the SHOW STATUS command to pull all the variables that start with “Qc”
mysql> SHOW STATUS LIKE 'Qc%';
Enable in Config File
Next we need to update /etc/my.cnf configuration file for MySQL.
Open the file and then add these values if they don’t already exist in the file.
query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576
Query caching can significantly improve the speed of your web application, especially if your application does mostly reads. Monitor the status using the methods above and see how it works over time.
