In the last episode (Jan 29), Rusch (ext) Reiner said:
> I've got one question which belongs to the new query cache in 4.01.
> I wonder why the cache should be deleted if there's a statement like
> update, insert etc. I think it must be considered that there are
> possible situations where this isn't the best way. For example:
> 1) select * from DATABASE where DATE = '2002-12-02'
> 2) update DATABASE set NAME = 'my name' where DATE = '2002-12-01'
> So 2) would kill the whole cache, but for 1) the update doesn't
> matter! Wouldn't it be better to look for what selects matters for
> the cache by updating something in the table???
For simple things like that, yes. But how do you determine what
matters? Say you have these queries already cached:
/* pretend your orignal record before your query 2) had name='fred' */
3) SELECT count(*) FROM database WHERE name='bob'
4) SELECT count(*) FROM database WHERE name='fred'
5) SELECT date FROM database WHERE name like '%ame%'
6) SELECT * FROM database MONTH(date) = '12'
7) SELECT incomerange FROM database order by name
You can keep the results of 3), but have to clear the rest. How do you
distinguish between 1), 3), and the rest? The current cache is fast
because it simply does a string comparison of the current query against
all cached queries. Forcing it to keep record IDs or field ranges for
each cached query would slow the lookup process to the point of