I can't access the CVS server right now, so I've attached the experimental index tweaks and sample queries for contribs and history.
-- brion vibber (brion @ pobox.com)
Contribs:
EXPLAIN SELECT old_namespace,old_title,old_timestamp,old_comment FROM old USE INDEX (usertext_timestamp) WHERE old_timestamp > '20030201000000' AND old_user_text='XXX' ORDER BY old_timestamp DESC LIMIT 50; | table | type | possible_keys | key | key_len | ref | rows | Extra | old | ref | old_timestamp,old_user_text | old_user_text | 255 | const | 1 | where used; Using filesort | | old | range | old_timestamp,usertext_timestamp | usertext_timestamp | 269 | NULL | 1 | where used; Using filesort |
EXPLAIN SELECT old_namespace,old_title,old_timestamp,old_comment FROM old USE INDEX (user_timestamp) WHERE old_timestamp > '20030201000000' AND old_user=1 ORDER BY old_timestamp DESC LIMIT 50; +-------+------+------------------------+----------+---------+-------+------+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+------------------------+----------+---------+-------+------+----------------------------+ | old | ref | old_timestamp,old_user | old_user | 4 | const | 1 | where used; Using filesort | +-------+------+------------------------+----------+---------+-------+------+----------------------------+ | old | range | old_timestamp,user_timestamp | user_timestamp | 18 | NULL | 1 | where used; Using filesort |
History:
EXPLAIN SELECT old_id,old_user, old_comment,old_user_text,old_timestamp,old_minor_edit FROM old USE INDEX (name_title_timestamp) WHERE old_namespace=0 AND old_title='Main Page' ORDER BY old_timestamp DESC LIMIT 50;
+-------+------+---------------+---------------+---------+-------------+------+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+---------------+---------+-------------+------+----------------------------+ | old | ref | old_namespace | old_namespace | 256 | const,const | 1 | where used; Using filesort | +-------+------+---------------+---------------+---------+-------------+------+----------------------------+ | old | ref | old_namespace,name_title_timestamp | old_namespace | 256 | const,const | 1 | where used; Using filesort |