Is this insane?

[Marcus Bointon]

On 3 Dec 2006, at 09:47, mike wrote:

> Does it sound crazy to have 30-50 gets per page load? I'm thinking of
> caching each row in a database basically treating it as an object
> based on the primary key. Then fetching the cached information based
> on a list of IDs (basically saving a query of SELECT allthedata FROM
> allthetables WHERE PK IN(key1,key2,key3))
>
> I mean, not all pages will have this - but pages with lists of data
> would; and each item in the list would basically be a row of precached
> data in the database; it would have to be sorted/ordered and such
> differently, so it wouldn't necessarily make sense to just cache the
> entire result set as-is, I'm thinking row-based would be the most
> effective way.

I posted about something similar a while ago, and I'm still not sure  
of the best way around it. If you have an object that goes and gets a  
list of some related objects, ideally you want to do this in a single  
trip to the DB. So here I'd ordinarily do a "select * from mytable  
where ...", then use that data to create my objects. Worst case, you  
do one big query. Using memcache you might instead do "select id from  
mytable where...", then do a "select * from mytable where id = n" for  
each object, but lookup in memcache first, worst case you do n+2  
queries. The only problem here is that cache misses can take your  
query count much higher, so there will have to be a trade-off - the  
number of queries into memcache vs the fact that you know you could  
get the whole lot in a single DB query. So, a naive implemetation  
based solely on getting lists of ids then getting full sets of values  
for individual records would normally give horrific performance, but  
memcache rescues that situation to a large extent. The question is  
how many memcache lookups can you do for the price of that one big  
query, and is it worthwhile? Or am I completely wrong?

In answer to your other question, you could cache multiple ordered  
lists of IDs with different keynames according to the selected order,  
like 'users:lastname,firstname', but still cache the data for each  
user record individually, that way you'd get a cache hit no matter  
how the data was ordered, and if you happened to pick an ordering  
you'd used recently, it wouldn't even have to do the main lookup.

Marcus
-- 
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
marcus at synchromedia.co.uk | http://www.synchromedia.co.uk/