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.

Version: 1.23.0
Severity: major