https://bugzilla.wikimedia.org/show_bug.cgi?id=61889
Bug ID: 61889
Summary: ApiQueryLogEvents STRAIGHT_JOIN queries slow
Product: MediaWiki
Version: 1.23-git
Hardware: All
OS: All
Status: NEW
Severity: major
Priority: Unprioritized
Component: API
Assignee: wikibugs-l@lists.wikimedia.org
Reporter: sprin...@wikimedia.org
CC: bjor...@wikimedia.org, bryan.tongm...@gmail.com,
roan.katt...@gmail.com, s...@reedyboy.net
Web browser: ---
Mobile Platform: ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
You are on the CC list for the bug.
_______________________________________________
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
