MySQL: increasing query cache hit rate.

Part 1: Expecting SQL queries.

The most common mistakes:

1. Using different cases

Incoming queries are compared to those in the query cache before parsing, so the following two queries are regarded as different by the query cache:
    SELECT * FROM tbl_name
    Select * from tbl_name

Advice: use database abstraction layer or stick to single coding style.

2. Call to date/time functions at database side

A query cannot be cached if it contains any of the functions shown in the following list:

CONVERT_TZ() 

CURDATE() 

CURRENT_DATE() 

CURRENT_TIME() 

CURRENT_TIMESTAMP() 

CURTIME() 

NOW() 

SYSDATE() 

UNIX_TIMESTAMP() with no parameters 

Advice(UPDATED): Move date/time function to your application and cut off the seconds or/and  minutes  or/and hour part.

(If your database server and your application on the same, or both configured to same timezone, and using ntp servers).

3. Call to RAND(), FOUND_ROWS() functions at database side

Same for previos case: Queries which contain these function cannot be cached.

Advice: You could found "RAND()" analog at any programming language. Use SQL_CALC_FOUND_ROWS() function instead FOUND_ROWS()

4. Merging huge request with tables with high changing rate

If a table changes, all cached queries that use the table become invalid and are removed from the cache. This includes queries that use MERGE tables that map to the changed table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

Advice: Spit it to 2+ requests. 

Two queries(the first one which contains "huge part", but cached, and the second one for table with high change rate) will work faster then one merged.

For more information: http://dev.mysql.com/doc/refman/5.0/en/query-cache-operation.html

-- Vladimir Getmanshchuk aka vladget