Re: eWeek Poll: Which database is most critical to your

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