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

[snip]
> 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.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
I did not read the article at all, but I am familiar with query cache
and in fact, I do it a lot (I work for a database company). Here is
how the algorithm works:

You intercept every incoming query and parse it. Any physical data
gets replaced with parameter markers. A 64 bit hash is formed from the
parsed query with the parameter markers removed. The hash is used as
an index into a skiplist which also stores the original query. After
all, if a client has a million dollar request, he won't be happy that
the unbelievably rare thing happened and the checksums agreed.

You can add a counter to the data in the skiplist so that you know how
often the query happens. The parsed query will only be useful to a
system that can save time from having a query prepared (most systems
call it preparing the query). I was kind of surprised to see that
PostgreSQL does not have a prepare stage in libpq. This can be a
very large speedup in query execution (for obvious reasons).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<