Yes, of course you are right there. I was being overly generic. Or, rather, I was being specific to the original posters problem, along with the rash of people asking questions about similar problems. <br><br>The reason why the query cache on mysql is dumped whenever anything on the table is updated, is because it is the safest option. You can of course, determine if something is not unsafe yourself, when using memcache in front of it, but then you have this problem... figuring out which keys are now invalid because of some new data that has been added.
<br><br>Something has to be the source of truth. In most cases it would be the database. In a normal real-life situation it is impossible to say that your memcache and your database will be entirely in sync, and that both can be the source of truth. For example, A node in your cache cluster may die, and you may lose 25% of the keys.. So if a key is missing, build it from the database.
<br><br>Therefore, your cache can only be considered "mostly true". If you've been very careful, it could be very near to be in sync with teh database... if it never goes offline, and none of your keys expire unexpectedly.. etc.
<br><br>My long winded point is.... It might be perfectly alright to cache your query results. But it should be an explicit decision to do that, for a specific reason. I would hate for people to have picked up memcached, plopped it on their servers and merely cache all the queries that they do, for a quick speed boost, because there can be ramifications that are difficult to overcome. Especially since you cant do a wildcard delete (yet, maybe ever).
<br><br>As far as speed boosts go... make sure you have appropriate indexes on your tables. You might be surprised how many programmers out there have built database apps, and never thought about indexes, because quite frankly, it works without it. Until you have a million records and your queries start taking 20 seconds to process... of course... the programmers easy fix for that is to add memcache in front of the queries :)
<br><br>Btw, I agree that spreading the query cache over a cluster is a very good reason.<br><br><div><span class="gmail_quote">On 8/3/07, <b class="gmail_sendername">Don MacAskill</b> <<a href="mailto:don@smugmug.com">
don@smugmug.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><br><br>Clint Webb wrote:<br>><br>> Why is everyone using memcache for the SQL query as a key? That is what
<br>> the query cache on your database is for. You gain very little doing it<br>> this way, except now you have to manage the invalidation yourself, which<br>> the query-cache does for you.<br>><br><br>While I have no experience with, say, PostgreSQL's or Oracle's query
<br>caches, MySQL's is terrible for this.<br><br>The way it invalides the cache is "if TABLE has changed in any way, get<br>rid of any cache entries referencing this TABLE":<br><br>INSERT INTO table (id) VALUES (1);
<br>SELECT * FROM table WHERE id=1;<br>// this query is now cached<br>INSERT INTO table (id) VALUES (2);<br>// the first SELECT is now not cached anymore, despite the 2nd insert<br>having nothing to do with it<br><br>You can, of course, do much better than this yourself if you goal is to
<br>cache certain SQL queries. Which is why some people do this, or<br>something like it, using memcached.<br><br>There are other reasons, too, like memory size. What if you want to<br>cache, say, 1TB of queries? Most of us don't have DB boxes with 1TB of
<br>RAM... But putting together 32 boxes with 32GB each is easily doable<br>(easily being relative to getting a 1TB server).<br><br>Don<br></blockquote></div><br><br clear="all"><br>-- <br>"Be excellent to each other"