How can I cache MySQL data using tags to delete the cache when needed?

Question

I've been searching the web for a long time and can't find any cache system that will allow me to add multiple tags to a cache to be able to delete it when the database is updated, does anyone know if something like that exists?

Let me give you more details on what I'd like to do. I have a PHP website that has very heavy queries to do in the MySQL database (more than 4000 queries for some pages to display). So the idea is to cache these queries to lighten the database usage. On the other side I need these informations to be updated as soon as possible when the database is.

The scope is clear only the caches that need to be updated and not empty all of them at once because there's been a slight change in a row that affects only one page. Is there anyway to work this out with tags?

I've already seen Fastcached and BrennoCache but the only allow 1 tag…

[EDIT]: I've finished by using FastCache, using TTL and taging all cache elements with a naming system to empty any cache wich I could need to empty. The cache has also been applied to the smallest information portion possible which means tones of caches but very little info caches. Meaning a lot easier to empty just the right bit. This way we were able to go from 4233 queries for one page to 17.

Answer 1

Microsoft has a cache called AppFabric that will let you assign a list of tags to an item that is in the cache. http://msdn.microsoft.com/en-us/windowsserver/ee695849.aspx

To your original question/problem ...

From my experience, I would definitely recommend not trying to tag items in cache for pro-active evictions.

I tried to do exactly what you are doing with AppFabric a few years ago and ran into many problems. For my example, let's consider a website that sells products. Each product has an ID.

Problems:

1. Products end up being all over our queries. We have a page for an individual product. Products in categories. It goes on and on. If we have a cached item that has 100 products, does it have 100 tags? This quickly becomes difficult to manage.

2. Let's imagine one product is in 100 different cached objects. In the time it takes us to find every cached item and evict them, the item might have changed again! In other words you will have to think about transactions when dealing with changing data. If you are in the middle of a "write" with the cache, do you block so another write doesn't compete?

3. Even if you manage to figure out the complexity from (2), I would argue it will still take time to execute. How do you reflect this to the user?

My advice for caching:

1. Always use a TTL and let items in the cache expire if you can. Try really hard to change your thinking and use this approach. If you can move 90% of the data to this approach, your code will be so much simpler and easier to maintain.

2. For items that you can't move to (1), make sure they are in the cache in one place. This means no need to tag items. It will take some re-factoring to make everything reference that item, but it's worth it. That way when you need to expire an item, you do one call and it's simple. Then once it's done everyone gets the latest data.

Answer 2

I would use memcached to store the processed results of the queries and have any database updating scripts to either clear the memcache tag or preferably regenerate them with up to date processed data.