Last modified: 2014-02-26 00:50:21 UTC
enwiki has been seeing a bunch of slow ApiQueryLogEvents queries: SELECT /* ApiQueryLogEvents::execute */ /*! STRAIGHT_JOIN */ log_type, log_action, log_timestamp, log_deleted, log_id, page_id, log_user, log_user_text, user_name, log_namespace, log_title, log_comment, log_params FROM `logging` LEFT JOIN `user` ON ((user_id=log_user)) LEFT JOIN `page` ON ((log_namespace=page_namespace) AND (log_title=page_title)) INNER JOIN `change_tag` ON ((log_id=ct_log_id)) WHERE (log_type != 'suppress') AND ct_tag = 'Possible self promotion in userspace' ORDER BY log_timestamp DESC LIMIT 11 SELECT /* ApiQueryLogEvents::execute */ /*! STRAIGHT_JOIN */ log_type, log_action, log_timestamp, log_deleted, log_namespace, log_title, ts_tags FROM `logging` LEFT JOIN `user` ON ((user_id=log_user)) LEFT JOIN `page` ON ((log_namespace=page_namespace) AND (log_title=page_title)) LEFT JOIN `tag_summary` ON ((log_id=ts_log_id)) INNER JOIN `change_tag` ON ((log_id=ct_log_id)) WHERE (log_type != 'suppress') AND ct_tag = 'VisualEditor' ORDER BY log_timestamp DESC LIMIT 11 ... and several other similar forms. In all cases the STRAIGHT_JOIN forces an index scan on logging.times index, or more rarely a range access on logging.type_time index. Both query plans hit tens of millions of rows and take many minutes. Removing the STRAIGHT_JOIN allows the MariaDB query optimizer to choose a plan that takes seconds. Often it includes a filesort step, but more importantly it allows "index condition pushdown" which makes the filesort cheap.
Change 115381 had a related patch set uploaded by Anomie: Remove STRAIGHT_JOIN from ApiQueryLogEvents https://gerrit.wikimedia.org/r/115381
Change 115381 merged by Springle: Remove STRAIGHT_JOIN from ApiQueryLogEvents https://gerrit.wikimedia.org/r/115381