Memcache db models to make search more efficient

Question

Hello,

I need to set up some kind of e-store with search functionality.

For every search request I got to query structure like this:

product:
-name
-tags
--tag
-ingredients
--ingredient
---tags
----tag
---options
----option
-----option details
-variants
--variant
---tags
----tag
---options
----option measure
----value
---price

Now imagine number of queries... Database is normalized (2nd level I guess). It seems to me that one obvious solution here is to store each fetched model result set (product set, ingredient set, attribute set, tag set etc.) in memory for a very long time (products and its attributes updated not so often and only by admin) and make query from there.

So what do you think? Is there a better way to reduce db queries count?

Another option I thought about is to use sphinx, but I don't need full-text search at all, just exact matches with tag-like fields.

Thank you in advance!

Answer 1

On my Google App Engine app I normally move things from the datastore to memcache and work with them there since querying for the data can take a lot of time. MemCache, in my case, returns the data and has less load on CPU than accessing the data which can go through a number of queries until it gets what it is looking for.

I would recommend setting a long timeout on your memcache so that memcache doesnt flush it more often than you are expecting. I think the maximum timout is up to 1 month but normally setting it for a couple days will suffice.

You can always add code to flush the memcache if the data for a product has been updated so that you do the DB hit again but only once this time