1. Yes, indeed.
2. True, though the above is merely a demo. You may wish to only invalidate specific rows
3. Yes and no. You can use this_query() to get the invocating query. If you’re up to parsing it, well, it can be done.

1) I guess you could let your function return a 1 in case the cache was just freshly revalidated? Or a timestamp of the last revalidation and then the client running the query could SELECT that field as well if it were interested in that information?

2) A TRUNCATE would be faster than DELETE FROM cache_sample_data; right? (IF you want to delete everything). Of course you could have logic that allows you to update only what has changed since the previous revalidation.

3) I guess it’s not possible to ONLY revalidate rows that the client is interested in (i.e. the rest of the WHERE clause in the client’s query) ?

Anyway, congrats for pulling this off!

I agree that the stored routine makes for an overhead. MySQL is very slow in interpreting stored routines.

The RAND is only used because it’s simplest to demonstrate with, and easiest to follow. It does not make sense to use RAND for real. Thus, the “10% of the time it will be even slower” is irrelevant. It should be “Once every #### times the query is even slower than it would without cache”.

OTOH, in a batch job, it would (could) cut down on overall system load and, on average, speed up the process.

If the RAND is replaced by some _cheap_ test for whether to refresh the cache, _and_ refreshing is costly, then “you pay the price only when you need to”. — this has applicability. But that implies that the SELECT is happening more often than changes to the underlying table. Guess what? The Query cache does an even better job of optimizing that case.

If there is a WHERE clause on the user’s query, that is not, and can not, be pushed down to the reload subquery. If it could, that would open up more doors for optimizations.

Maybe I will find a use for this eventually. It is quite clever!

@Jonathan,
Take time-based invalidation, which is commonly used, such with memcached. Data is valid for the next 5 minutes. Put the readtime somewhere.
It’s simple, yet surprisingly useful, in my opinion.

I like the idea, but I have a bad feeling about it from past experiences.
A lot depends on the cache invalidation heuristic you pointed out. If it is inefficient, you just have a lot of IO overhead where it was not needed.

Can you recommend some good cache invalidation techniques to go with this idea?

Brilliant solution 🙂

There’s a lot of moving parts but I think this is going to be worth it if we can get better information_schema performance and reliability with this.