Speeding up MySQL database with query_cache and improving the performance

Is your MySQL database getting slower? Your server overloading? You might need to optimize your MySQL database server by enabling query caching which will increase the performance and increase the access time.

There are two ways to enable database caching:

1. From MySQL command or phpMyAdmin.
2. From MySQL configuration file, my.cnf.

1- From MySQL Command

First, let’s check if query_cache is enabled:

The three values that we’re going to work on are:

query_cache_type 

• A value of 0 or OFF prevents caching or retrieval of cached results.
• A value of 1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.
• A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.

query_cache_size

This is the cache size in bytes, if we want to add 32MB cache, we write the following value:
query_cache_size = 33554432
or
query_cache_size = 32MB

query_cache_limit

This is the maximum size of individual query results that can be cached. If you have big query results, you might need to increase this value. The default value is 1MB.

Most cPanel installations, virtualmin, webmin, plaxo or plesk have database caching enabled, but have query_cache_size set to 0, which basically mean the queries are not getting cached.

To enable query cache for 32MB issue the following MySQL command:

To make sure that the value is set correctly, write the following:

2- From MySQL Configuration File “my.cnf”

This method might be harder than the previous one, because you will have to change the configuration file, however in this method the configurations won’t be lost even if the MySQL database server is restarted.

You need to be logged in as root to change the configuration file. if root user is disabled, you can use the su command. More about linux commands can be found in here.

The MySQL database configuration file is called my.cnf

You can find the MySQL configuration file my.cnf in the following directories, and they are read in the given order in linux:
/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

In windows
C:Windowsmy.ini
C:Windowsmy.cnf
C:my.ini
C:my.cnf
C:Program FilesMySQLMySQL Server 5.5my.ini
C:Program FilesMySQLMySQL Server 5.5my.cnf

To enable caching just open the my.cnf file (in linux/ssh):

And edit the following 3 lines:

If those value don’t exist in the configuration file, you can append them. Just make sure you’re appending them in the [mysqld] section.

Make sure to use “Insert” to edit the file, and “ESC” to exit editing mode, and “:w” to write/save the changes.

Don’t forget to restart your MySQL after applying the changes:
To restart MySQL in CentOS:

Any questions or suggestions, please leave a comment below.