Query Cache in MySQL

Written by Mike on October 30, 2008 – 8:12 am -

What is Query Cache and How it works?

One of the best ways to speed up your web application is to enable query caching in your database, which caches commonly used SQL queries in memory for virtually instant access by the next page that makes the same request.

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

How to enable caching in MySQL?

Query Cache Can be Enabled by two ways
1) Using MySQL Queries
2) Using MySQL Configuration file i.e. my.cnf

1) Enabling Query Cache by executing query:

Login to MySQL prompt

[root@server ~]# mysql -u root –p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 16 to server version: 

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

Now setup cache size 16Mb (16*1024*1024=16777216):

mysql> SET GLOBAL query_cache_size = 16777216;
Query OK, 0 rows affected (0.00 sec)

You can check the cache size by executing following query:

mysql> SHOW VARIABLES LIKE 'query_cache_size';
 +------------------+----------+
 | Variable_name    | Value    |
 +------------------+----------+
 | query_cache_size | 16777216 |
 +------------------+----------+
mysql>


2) Enabling Query Cache in MySQL Configuration (my.cnf)

You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file. Open my.cnf uing vi or pico editor and insert following into it:

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.


Tags: ,
Posted in Dedicated Server Hosting, Linux VPS Hosting | 1 Comment »

One Comment to “Query Cache in MySQL”

  1. Biggest Halloween » Blog Archive » Query Cache in MySQL | Says:

    [...] unknown wrote an interesting post today onQuery Cache in MySQL |Here’s a quick excerptWhat is Query Cache and How it works? One of the best ways to speed up your web application is to enable query caching in your database, which caches commonly. [...]

Leave a Comment

RSS

  • Subscribe Me

  • Tag Cloud

  • Archives