Re: Caching of Queries

On Mon, 27 Sep 2004 15:03:01 +1000, Neil Conway <neilc@samurai.com> wrote:
> 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.

I think it's important to demark three levels of possible caching:
1) Caching of the parsed query tree
2) Caching of the query execute plan
3) Caching of the query results

I think caching the query results (3) is pretty dangerous and
difficult to do correctly.

Caching of the the execute plan (2) is not dangerous but may actually
execute more slowly by caching a bad plan (i.e. a plan not suited to
the current data)

Caching of the query tree (1) to me has very little downsides (except
extra coding). But may not have a lot of win either, depending how
much time/resources are required to parse the SQL and lookup the
objects in the system tables (something I've never gotten a
satisfactory answer about). Also, some of the query cache would have
to be cleared when DDL statements are performed.

-Scott