Re: [Wikitech-l] Slow query log

On sab, 2003-02-08 at 14:26, Brion Vibber wrote: 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)