When to use memcached

Question

I sorta get how memcached works. You use it store chunks of data to improve site performance. When you want to retrieve some data you check if its in memcached first, if it is, then you retrieve it, otherwise you check your database/filesystem etc.

I just don't know how/when to use it? What would be a good opportunity?

I have the following tables:

Author:

id username email password salt email_salt email_verified ip_address

Author_threads:

thread_id, author_id

Thread:

id, title, content, created

Tag:

id, name

Thread_tags:

tad_id, thread_id

I want to select the latest 30 threads, their author and all their tags. This is the SQL statement I use:

       SELECT thread.title, thread.id as thread_id,
       thread.content, author.username, author.id as author_id,
       GROUP_CONCAT(DISTINCT tag.name ORDER BY tag.name DESC SEPARATOR ',') AS tags
       FROM thread 
       JOIN thread_tags ON thread.id = thread_tags.thread_id
       JOIN tag ON thread_tags.tag_id = tag.id

       JOIN author_threads ON thread.id = author_threads.thread_id
       JOIN author ON author_threads.author_id = author.id

       GROUP BY thread.id DESC
       LIMIT 0, 30

This is the PHP that I use:

function get_latest_threads($link, $start)
{

   $start = minimal_int($start);

   $threads = sql_select($link, "SELECT thread.title, thread.id as thread_id,
                                 thread.content, author.username, author.id as author_id,
                                 GROUP_CONCAT(DISTINCT tag.name ORDER BY tag.name DESC SEPARATOR ',') AS tags
                                 FROM thread 
                                 JOIN thread_tags ON thread.id = thread_tags.thread_id
                                 JOIN tag ON thread_tags.tag_id = tag.id

                                 JOIN author_threads ON thread.id = author_threads.thread_id
                                 JOIN author ON author_threads.author_id = author.id

                                 GROUP BY thread.id DESC
                                 LIMIT $start, 30"  # I only want to retrieve 30 records each time
                        );

   return $threads;

}

Where/how would memcached be uses here?

score 11 · Accepted Answer · answered Mar 27 '11 at 19:16

I just don't know how/when to use it?

Use it only once you have proved that adding caching is the best performance boost you can get. Adding data caching or output caching to a complex application that previously did not have any sort of caching can uncover a large number of subtle bugs and bizarre behavior.

Use a code profiler first. Find out where your code is having real performance problems. Identify the bottlenecks and fix them. If that fix involves caching, so be it, but gather evidence first.

Many thanks for the idea of using the code profiler. – kta Jan 22 '15 at 23:18 — kta, Jan 22 '15 at 23:18

Alexander Gessler · Answer 2 · 2011-03-27T19:08:36.730

1

The list of latest threads will be probably be requested by a lot of your site users, so caching the entire SQL result using memcached sounds like a perfect opportunity.

A very simple approach to memcached is to use SQL queries as keys and their respective results as values (i.e. see this tutorial here). You might want to try that first on all high-frequency database queries and profile the result before you start optimizing any further.

edited Mar 27 '11 at 19:08

answered Mar 27 '11 at 19:02

Alexander Gessler

45,603
7
82
122

Doesn't SQL do that type of query caching already? Or is the cache expiry time very short there? – Tesserex Mar 27 '11 at 19:06

score 1 · Answer 3 · answered Mar 27 '11 at 19:05

There's no set guidelines on when to use it. You have to figure out which database queries are most frequent and / or most costly, and cache those. In your case, I would probably cache the result of that function call.

Where I would get confused (as you may be) is what do do as new threads are created. Your query is going to give different results every time someone creates a thread. So what you should do in that case, when someone creates a thread, is to update the db, and then adjust your result set in cache by kicking out the oldest thread and adding the new one. You won't even need to reload the newest 30 threads from cache, since it will be updated.

When to use memcached

3 Answers3