On Tue, 2002-02-26 at 19:08, Dann Corbit wrote:
> Statistical tools are a good idea, because they can tell us where
> indexes should be added. However, you cannot simply return the result
> of the previous query, because the contents may have changed since the
> last time it was executed. It is simply invalid to do that. If some
> other system is doing that, then it isn't a relational database.
No -- as I said, any inserts, updates or deletes that affect the table
in question will cause a full cache flush.
> How do you know whether or not someone has affected the row that you
> are reading? If you do not know, then every single update, insert or
> delete will mean that you have to refresh.
Yes, that is true.
> And not only that, you will
> also have to track it. For sure, it will make the whole system run
> more slowly rather than faster.
I don't think tracking changes imposes a lot of overhead -- it is
relatively simple to determine if a query affects a given table.
> Very likely, it is only my limited understanding not really grasping
> what it is that you are trying to do. Even so, I don't think it really
> helps even for read only queries, unless it is exactly the same query
> with the same parameter markers and everything that was issued before.
> That is very unusual. Normally, you won't have the entire query hard-
> wired, but with allow the customer to do some sort of filtering of the
> data.
Hmmm... the more I think about it, the more unusual it would be for
_exactly_ the same query to be repeated a lot. However, the article
reported a significant performance gain when this feature was enabled.
That could mean that:
(a) the performance measurements/benchmarks used by the article were
synthetic and don't reflect real database applications
(b) the feature MySQL implements is different than the one I am
describing
When I get a chance I'll investigate further the technique used by MySQL
to see if (b) is the case. However, it is beginning to look like this
isn't a good idea, overall.
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
