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.