Re: Caching of Queries

On Thu, 2004-09-23 at 07:43, Aaron Werman wrote:
> MySQL stores a statement with its results. This is optional and when a
> client allows this type of processing, the SQL is hashed and matched to the
> statement - and the stored *result* is returned. The point is that a lot of
> systems do lots of static queries, such as a pick list on a web page - but
> if the data changes the prior result is returned. This (plus a stable jdbc
> driver) was the reason MySQL did well in the eWeek database comparison.

I think the conclusion of past discussions about this feature is that
it's a bad idea. Last I checked, MySQL has to clear the *entire* query
cache when a single DML statement modifying the table in question is
issued. Not to mention that the feature is broken for non-deterministic
queries (like now(), ORDER BY random(), or nextval('some_seq'), and so
on). That makes the feature close to useless for a lot of situations,
albeit not every situation.

-Neil