List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 29 2003 11:36pm
Subject:Re: query cache
View as plain text  
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
uselessness.

-- 
	Dan Nelson
	dnelson@stripped
Thread
query cacheext) Reiner29 Jan
  • Re: query cacheDan Nelson30 Jan
    • Storage issueJonas Ask√•s30 Jan
      • Re: Storage issueDavid Bordas30 Jan
      • RE: Storage issueJohnny Withers30 Jan
      • Re: Storage issueDan Nelson30 Jan