MySQL Query Cache

[Ryder]

hi
i have started some work on sql while working on that i am out of time and need to go fast please any one here explain me about MySQL Query Cache.if any one can explain better i will appreciate that.
thank you.

[opaper]

The Query cache saves the text of a query with the result that was returned to the client. If a request is called by the same result, MySQL will return the result from the cache instead of executing the query again. It is important to note that the query cache does not return data because it is expired systematically erased every time the cached data is modified.

The cache is extremely useful when the content changes little tables and there is a series of identical queries to execute. The gains in performance can achieve 238% to research a column. MySQL to go for the results of a query in the cache, should the latter is identical to that of a request already processed. No difference, however minor or it is tolerated.

Example: for both MySQL queries are not identical when it comes to seek the answer in the cache.

SELECT * FROM tbl_name
Select * from tbl_name

SELECT with the developer can specify whether it wants the result to be cached. Thus:

* A query like SQL_CACHE SELECT id, name FROM customer caches the result.
* As against the SQL_NO_CACHE SELECT id, name FROM customer does not result in the cache.

NB: To enable the query cache you must configure the my.cnf file by assigning the value 1 to the property query_cache_type.

[MindSpace]

Enabling MySQL Query Cache

Edit your my.cnf and set query_cache_type equal to 1, and set the query_cache_size to some value (here we have set it to 25mb)
query_cache_type = 1
query_cache_size = 26214400

If either query_cache_type or query_cache_size are set to zero caching will not be enabled. If you have lots of RAM on your server you may want to increase the size of the cache accordingly. There are some more settings you can tweak but these will get you going.
" Note you can also edit these settings using MySQL Administrator. They can be found under Health > System Variables > Memory > Cache "

Cache Hints

You can also set query_cache_type = 2 - with this setting queries are only cached if you pass the hint SQL_CACHE to them, for example:
SELECT SQL_CACHE something FROM table

Alternatively, if you have query_cache_type = 1, you can tell MySQL that you don't want a specific query to be cached. This is highly recommended because you don't want to fill up the cache with highly dynamic queries (such as a search form). This is done with the hint SQL_NO_CACHE.
SELECT SQL_NO_CACHE stuff FROM table

[Modifier]

As we know, speed is always the most important element in developing a website especially for those high traffic database driven website. You can try to turn on query cache to speed up query.

To speed up query, enable the MySQL query cache, before that you need to set few variables in mysql configuration file (usually is my.cnf or my.ini)


- 1st, set query_cache_type to 1. (There are 3 possible settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).

query-cache-type = 1

- 2nd, set query_cache_size to your expected size. I’d prefer to set it at 20MB.

query-cache-size = 20M

If you set your query-cache-type = 2 ( on demand ), you would wan to modify your sql query to support cache.

SELECT SQL_CACHE field1, field2 FROM table1 WHERE field3 = ‘yes’

To check if your mysql server already enable query cache, simply run this query:-

SHOW VARIABLES LIKE ‘%query_cache%’;