MySQL Query Cache - what you probably didn’t know

I decided to write this post during development of new Download3000 site engine because I think it is the thing that many developers simply forgot to consider when they develop new applications based on MySQL database server.

If you run a busy site, MySQL query cache is a must. It can dramatically decrease server load and save system resources for other services you run on the same server.
When it comes to cache size, small sites can use values under 32MB but if your site is really busy (tens of thousands page views a day) and you use big number of different applications you can set higher value. You can set it to 64MB or go ever higher to e.g. 128MB. Of course it depends not only on your requirements but also how much RAM you have installed on server and you must consider also what other services you run on same machine. You simply cannot dedicate all RAM to MySQL if your server is running also Apache.

And now why I wrote this post…
Developers usually forget to keep query variations on low number. What does it mean ?
Here is a simple example… Let’s have a table with customers where we have these columns:
name, surname, number of orders, address
Consider we have two pages…

1. One is displaying information about customer orders, so we use SQL query:

SELECT name, surname, num_of_orders FROM customers WHERE customer_id = '1000'

2. Second is displaying information about customer, so we use SQL query:

SELECT name, surname, address FROM customers WHERE customer_id = '1000'

What will happen ? MySQL will run query #1 and store result set in query cache. Then for second page it’ll run query #2 and store result set in query cache. As you can see we have 2 duplicate columns stored in query cache: name, surname for the same customer_id = ‘1000′ . It’s overhead on for your MySQL server and you should avoid it if possible because it consumes space in your query cache.
MySQL is unable to think about queries, it just simply cache the results.

So what to do ? You should lower variability of queries if possible. I mean for our example you can use following query:

SELECT name, surname, num_of_orders,address FROM customers WHERE customer_id = '1000'

As you can see we store 4 columns in query cache now instead of 3 + 3 = 6 columns (if we use two separate non identical queries). So we saved 2 columns and it means we saved space in query cache that can be used for other query result sets.
This was a very simple example but if you run duplicate queries with large data sets (e.g. TEXT columns) it could save a huge amount of space in your query cache.