Difference between mysql cache and memcached

Question

I have 1 mysql table which is controlled strictly by admin. Data entry is very low but query is high in that table. Since the table will not change content much I was thinking to use mysql query cache with PHP but got confused (when i googled about it) with memcached.

• What is the basic difference between memcached and mysqlnd_qc ?
• Which is most suitable for me as per below condition ?
• I also intend to extend the same for autcomplete box, which will be suitable in such case ?

My queries will return less than 30 rows mostly of very few bytes data and will have same SELECT queries. I am on a single server and no load sharing will be done. Thankyou in advance.

Answer 1

If your query is always the same, i.e. you do SELECT title, stock FROM books WHERE stock > 5 and your condition never changes to stock > 6 etc., I would suggest using MySQL Query Cache.

Memcached is a key-value store. Basically it can cache anything if you can turn it into key => value. There are a lot of ways you can implement caching with it. You could query your 30 rows from database, then cache it row by row but I don't see a reason to do that here if you're returning the same set of rows over and over. The most basic example I can think of for memcached is:

// Run the query
$result = mysql_query($con, "SELECT title, stock FROM books WHERE stock > 5");

// Fetch result into one array
$rows = mysqli_fetch_all($result);

// Put the result into memcache.
$memcache_obj->add('my_books', serialize($rows), false, 30);

Then do a $memcache_obj->get('my_books'); and unserialize it to get the same results.

But since you're using the same query over and over. Why add the complication when you can let MySQL handle all the caching for you? Remember that if you go with memcached option, you need to setup memcached server as well as implementing logic to check if the result is already in cache or not, or if the records have been changed in the database.

I would recommend using MySQL query cache over memcached in this case.

One thing you need to be careful with MySQL query cache, though, is that your query must be exactly the same, no extra blank spaces, comments whatsoever. This is because MySQL does no parsing to determine compare the query string from cache at all. Any extra character somewhere in the query means a different query.

Peter Zaitsev explained very well about MySQL Query Cache at http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/, worth taking a look at it. Make sure you don't need anything that MySQL Query Cache does not support as Peter Zaitsev mentioned.