Last modified: 2005-04-02 00:24:58 UTC
Queries like this are quite common:
wikiuser dewiki 158 SELECT COUNT(*) AS n FROM cur
MySQL has a query cache which will return the result
very quickly if the query string is an exact match
(including case) of a query in the cache. At present
these queries require a count to the nearest second,
making the cache almost useless for them. Replacing
the last three or four digits with 0 would allow the
query cache to cache many of them. Even two digits
would help en on Wikipedia. Suggest rounding in this
way for any query where an approximate value is good
I think that the example above is from a watchlist.
This particular query is used for the watchlist to get an idea of which of two ways of
pulling data will be more efficient. Really it's a sucky hack anyway; an improved watchlist
query could make this question obsolete.
Since we're seeing edits just a few seconds apart anyway, I wonder just how much
opportunity there is to cache. Are watchlist loads more frequent than edits?
Watchlist queries aren't more frequent than edits but they
are far more of a problem. An edit is usually insignificant
load-wise, at present, while this (and other) watchlist
queries can run for many tens of seconds or even a minute or
more and cause a problem for other queries, including other
This particular watchlist query is deletable - the query is
actually slower than the one it's trying to protect against.
On en there are few watchlists over 10,000 items and the
whole thing is in the hundreds of thousands of articles, so
there's no need to worry about someone watching a high enough
percentage of articles for a full cur scan to be worth doing.
If there is worry that it's needed, the rounding approach to
this and anything similar is the way to go as a quick change
I don't understand how the query cache is supposed to be invoked by the proposed rounding if the queries are less frequent than edits. The query
cache will be blown away by every edit changing the cur table, won't it?
It is invalidated when any involved table is changed. It's an odds game. These
are often slow, so whether one will be so slow that it won't complete before the
table is changed and whether another one will come in in the remaining available
time is a question to wonder about. For en the answer will often be no. For
other wikis, the run time is lower, the edit rate is lower and the odds are
better, freeing up mor eresources for the en ones and other queries which the
server has to deal with. Some chance beats no chance and at 2,000 queries per
second, we'll get lucky sometimes.
That query is removed from REL1_4 branch, as useless. Watchlists are served from
recentchanges tables now.