Porting SQL results into memcached

Question

I have few tables which are accessed frequently by users. The same kind of queries are running again and again which cause extra load on the server.

The records do not insert/update frequently, I was thinking to cache the IDs into memcached and then fetch them from database this will reduce the burden of searching/sorting etc.

Here is an example

SELECT P.product_id FROM products P, product_category C WHERE P.cat_id=C.cat_id AND C.cat_name='Fashion' AND P.is_product_active=true AND C.is_cat_active=true ORDER BY P.product_date DESC

The above query will return all the product ids of a particular category which will be imported into memcached and then rest of the process (i.e., paging) will be simulated same as we do with mysql result sets.

The insert process will either expire the cache or insert the product id on the first row of the array.

My question is this the practical apporach? How do people deal with searches say if a person is searching for a product which returns 10000 results (practically may not possible) do they search every time tables? Is there any good example available of memcached and mysql which shows how these tasks can be done?

Answer 1

Hello,

you may ask yourself if you really need to invalidate the cache upon insert/update of a product.

Usually a 5 minutes cache can be acceptable for a product list.

If your invalidation scheme is time-based only (new entries will only appear after 5 min) there is a quick&dirty trick that you can use with memcache : simply use as a memcache key an md5 of your sql query string, and tell memcache to keep the result of the select for 5 minutes.

I hope this will help you