On sab, 2003-02-08 at 14:26, Brion Vibber wrote:
Slow query log from Friday morning to present: http://www.wikipedia.org/tools/slowquerylog.gz
Now extends through, well, the present present.
Remaining things that need to be made less ugly slow are: recentchanges, search, watchlist.
Recentchanges looks okay, except it should benefit from the inverse timestamp trick (or Mysql4 upgrade); this would allow an index sort.
Search has been discussed elsewhere...
The watchlist:
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=7457 AND wl_title=cur_title AND (cur_namespace=wl_namespace OR cur_namespace=wl_namespace+1) ORDER BY inverse_timestamp LIMIT 100;
+-----------+------+----------------------------------------------+-----------+---------+--------------------+------+----------------------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+------+----------------------------------------------+-----------+---------+--------------------+------+----------------------------------------------------------+ | watchlist | ref | wl_user | wl_user | 4 | const | 1730 | where used; Using index; Using temporary; Using filesort | | cur | ref | cur_namespace,cur_title,name_title_timestamp | cur_title | 255 | watchlist.wl_title | 1 | where used | +-----------+------+----------------------------------------------+-----------+---------+--------------------+------+----------------------------------------------------------+
Explain doesn't give any different results if the namespace+1 is taken out; so adding near-dupe rows to cover talk pages wouldn't help.
I'm not quite sure how to match up the indexes right. Note that watchlist has a composite index on wl_user, wl_namespace, and wl_title... but I don't know if it can match those up if part 1 of the index is a constant, and parts 2 and 3 match 1 and 2 of the other table's index.
Some prolific users have three thousand or more titles in their watchlist; the total number of distinct watched pages on the en.wiki is 30409.
Is there any way we could do this without temporary tables & filesorts & whatnot?
-- brion vibber (brion @ pobox.com)