If you have a large MySQL database with high traffic and too many queries are loading the best thing to do is enable “mysql query caching”.
In order to enable the MySQL query cache, you will need to set some variables in mysql configuration file which in most cases is called my.cnf but not all server have it enabled so you will have to either create a new one or copy existing .cnf files.
Usually located under any of these directories:
/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
[datadir]/my.cnf
~/.my.cnf
If you can’t find it there then move try running a shell command locate my.cnf and what this does is look for that file so your can find the location and edit it but if it does not return any results you can try and see if any of the following files exist:
my-medium.cnf
my-small.cnf
my-large.cnf
Most likely one of them will return so just copy the my-medium.cnf(for normal or medium size databases) file to the /etc/ directory and rename it to my.cnf
Now open up your newly created my.cnf file (command vim my.cnf )
That will open up the following file but **note that this is on a localhost so the configuration file might be different on your server.
Sample MySQL config file for medium systems.
#
# This is for a system with little memory (32M – 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /Applications/MAMP/db/mysql) or
# ~/.my.cnf to set user-specific options.
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the “–help” option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /Applications/MAMP/tmp/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /Applications/MAMP/tmp/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#added for query cache (added)
query-cache-type = 1(added)
query-cache-size = 10M(added)
# Don’t listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the “enable-named-pipe” option) will render mysqld useless!
“/Applications/MAMP/Library/my.cnf” 158L, 5018C
Now you will need to enable the query_cache by just adding these two lines of code :
query-cache-type = 1
query-cache-size = 10M
or if that does not work try this :
SELECT SQL_CACHE field1, field2 FROM table1 WHERE field3 = ‘yes’
And to check see if your mysql server has enabled query cache, simply run this query:
SHOW STATUS LIKE ‘%qcache%’;
a result similar to this will show up:
+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_queries_in_cache | 1 |
| Qcache_inserts | 3 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20947592 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4 |
+————————-+———-+
And you can adjust the settings as you see go along. Thats it!
If you have any questions or like us to set this up for you please
contact us 
