On Sat, Feb 01, 2003 at 11:10:21AM -0800, Brion Vibber wrote:
Also, a vaguely related question on the watchlist query. Currently the
return-whole-watchlist query looks like:
SELECT DISTINCT
cur_id,cur_namespace,cur_title,cur_comment,
cur_user,cur_user_text,cur_timestamp,cur_minor_edit,cur_is_new
FROM cur,watchlist
WHERE wl_user={$uid} AND wl_title=cur_title
AND (cur_namespace=wl_namespace OR cur_namespace=wl_namespace+1)
ORDER BY cur_timestamp DESC {$dolimit}
I would suggest the same trick here as in my previous mail, but you would
have to split up the query for each namespace and then in PHP do some
postprocessing by merging the two lists and cutting it off if the total sum
is beyond the requested limit.
And I'm
not sure if we really need the resulting cur_id. If we don't,
then indeed only the index is needed and that would really speed up the
query.
99% of the time, we don't; we're just checking for existence. It should
be sufficient to just have a boolean 'exists/doesn't exist' field. When
we're checking existence, is it most efficient to SELECT the indexed
field, or COUNT(*), or what?
Just a SELECT 1 FROM ... wil do. And as I said, in that case only the index
is needed to answer the query and since the caching rate for the indices is
very high this should really speed up the queries for the links in the
documents.
-- Jan Hidders