cftopper.com

The Query Cache

If your using MySQL in production you should turn on the query cache - this is off by default ( even if you have installed using the wizard and choosen "dedicated server" ).

The query cache basically remebers the results of your queries and saves them from being executed twice unnecessarily resulting in huge performance improvements.

Bare in mind that any inserts or updates will reset the parts of the query cache that relate to the affect tables. Luckily most applications read from the table much more often that they write data into them.

How to turn on the Query Cache

1. Open your my.ini (or my.cfg) file in notepad and set query_cache_type to 1. There are 3 settings for this: 0 (disable / off), 1 (enable / on) and 2 (on demand).
   Tip: If you can't find this in your config file, so just add it in.
   
2. Now you need to tell MySQL how much memory space to use. For this, find (or add) the query_cache_size option and set it to something that makes sense. This is a global setting so all sites and applications on the server will share this. You could try 30MB to start with
   query-cache-size = 20M
   
3. You will need to restart MySQL for the caching to begin.
   

How to know of the cache is being used

After restarting your server, execute the following SQL:

SHOW STATUS LIKE 'q%';

You will get something like:

Qcache_free_blocks    1613
Qcache_free_memory    202554688
Qcache_hits    679493
Qcache_inserts    207317
Qcache_lowmem_prunes    0
Qcache_not_cached    4532
Qcache_queries_in_cache    3488
Qcache_total_blocks    8788

The important two are Qcache_queries_in_cache and Qcache_not_cached.

Qcache_queries_in_cache is the total number og queries that are currently cached in memory.

Qcache_not_cached is the total number of queries that were executed where MySQL could not use a cached version.

References

• http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
• http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html

Here's an important tip for everybody using MySQL, you need to set the following option in your my.ini or my.cnf configuration file to prevent SQL injection attacks.

sql-mode=NO_BACKSLASH_ESCAPES

Before you do this, If you have a simple search on your site and a user inserts

test\'' OR 1 = 1   --

instead of just

test

The \'' will get escaped and your SQL statment will turn from something like:

WHERE variable LIKE 'test' AND userId = 55;

to something like:

WHERE variable LIKE 'test' OR 1 = 1 -- AND userId = 55;

Because -- is the start of a a comment in MySQL, the AND userId = 55 bit will be ignored.

I stumbled upon this last night and it's excellent.



If your too lazy to watch this, just take this one tip from me - use the MySQL Administrator tool to increase the Cache Size setting under the Performance tab. You will notice immediate speed-up on heavily-loaded servers where the 32Mb cache just wasn't enough.

If you ever run into the error message "MySQL error on rename 150" when making changes to your MySQL table, the easiest thing to do to fix this is:

1. Backup the database
2. Delete it
3. Recreate it
4. Restore it

Basically MySQL has temporary files interfering with the ALTER TABLE process. I has done a load of googling and found comments but no solution - this is posted here to save others some stress.

Update

Be careful with the above advise - you could be getting this error just because you are doing something like trying to link an unsigned int to a signed int. Use the advise above only after you have carefully determined that MySQL is acting the maggot.