Re: Database Caching

> > I wonder how this sort of query result caching could work in
> > our MVCC and visibility world at all. Multiple concurrent
> > running transactions see different snapshots of the table,
> > hence different result sets for exactly one and the same
> > querystring at the same time ... er ... yeah, one cache set
> > per query/snapshot combo, great!
> >
> > To really gain some speed with this sort of query cache, we'd
> > have to adopt the #1 MySQL design rule "speed over precision"
> > and ignore MVCC for query-cached relations, or what?
>
> Actually, you are missing, I think, as is everyone, the 'semi-static'
> database ... you know? the one where data gets dumped to it by a script
> every 5 minutes, but between dumps, there are hundreds of queries per
> second/minute between the updates that are the same query repeated each
> time ...
>
> As soon as there is *any* change to the data set, the query cache should
> be marked dirty and reloaded ... mark it dirty on any update, delete or
> insert ...
>
> So, if I have 1000 *pure* SELECTs, the cache is fine ... as soon as one
> U/I/D pops up, its invalidated ...

The question is, when it's invalidated, how does it become valid again?
I don't see that there's a way to do it only by query string that doesn't
result in meaning that the cache cannot cache a query again until any
transactions that can see the prior state are finished since otherwise
you'd be providing the incorrect results to that transaction. But I
haven't spent much time thinking about it either.