Last modified: 2010-05-15 15:33:38 UTC
From the slow query log of holbach: # Query_time: 1 Lock_time: 0 Rows_sent: 50 Rows_examined: 28229 SELECT log_type, log_action, log_timestamp, log_user, user_name, log_namespace, log_title, cur_id, log_comment, log_params FROM `user`, `logging` LEFT OUTER JOIN `cur` ON log_namespace=cur_namespace AND log_title=cur_title WHERE user_id=log_user ORDER BY log_timestamp DESC LIMIT 0,50; The explain: explain SELECT log_type, log_action, log_timestamp, -> log_user, user_name, -> log_namespace, log_title, cur_id, -> log_comment, log_params FROM `user`, `logging` LEFT OUTER JOIN `cur` ON log_namespace=cur_namespace AND log_title=cur_title WHERE user_id=log_user ORDER BY log_timestamp DESC LIMIT 0,50\G *************************** 1. row *************************** table: logging type: ALL possible_keys: user_time key: NULL key_len: NULL ref: NULL rows: 27972 Extra: Using filesort *************************** 2. row *************************** table: user type: eq_ref possible_keys: user_id key: user_id key_len: 4 ref: logging.log_user rows: 1 Extra: *************************** 3. row *************************** table: cur type: ref possible_keys: cur_title,name_title_timestamp key: cur_title key_len: 255 ref: logging.log_title rows: 1 Extra: Note the type: ALL for the logging table and the Extra: Using filesort as well as the estimated 27972 rows. It's doing a full table scan because there's no index which lets it get the records in timestamp order. I suggest adding an index log_timestamp (log_timestamp) . Assuming that makes sense, let me know and I'll put it live on the Wikimedia servers next low load time. It's also using the wrong index for cur but that may clear up after this change.
The index times (log_timestamp) is present on enwiki but not plwiki or frwiki. Is this logging query still used? If it is, we'll need that index.
We've got a pure index on log_timestamp, and it's also included in all the other indexes on that table, now. Closing as FIXED.
