[Wikidata-bugs] [Maniphest] [Commented On] T207313: Some administrative and log actions on Wikidata take longer than 60 seconds and time out

[Ladsgroup]

Ladsgroup added a comment.

Looking at tendril and running the changed sql queries on prod, my patch should fix it.
The old query was like this:

SELECT /* IndexPager::buildQueryInfo (LogPager) */ log_id, log_type, log_action, log_timestamp, log_namespace, log_title, log_params, log_deleted, user_id, user_name, user_editcount, COALESCE( comment_log_comment.comment_text, log_comment ) AS `log_comment_text`, comment_log_comment.comment_data AS `log_comment_data`, comment_log_comment.comment_id AS `log_comment_cid`, log_user, log_user_text, NULL AS `log_actor`, (SELECT GROUP_CONCAT(ct_tag SEPARATOR ', ') FROM `change_tag` WHERE (ct_log_id=log_id) ) AS `ts_tags` FROM `logging` LEFT JOIN `comment` `comment_log_comment` ON ((comment_log_comment.comment_id = log_comment_id)) LEFT JOIN `user` ON ((user_id=log_user)) WHERE (log_action != 'revision') AND (log_action != 'flow-delete-post') AND (log_action != 'flow-delete-topic') AND (log_action != 'flow-restore-post') AND (log_action != 'flow-restore-topic') AND (log_type NOT IN ('spamblacklist', 'titleblacklist', 'abusefilterprivatedetails', 'suppress')) AND log_type IN ('delete', 'move', 'protect') AND log_namespace = '0' AND log_title = 'Q56399360' AND ((log_deleted & 1) = 0) ORDER BY log_timestamp DESC LIMIT 11;

Which is now changed to:

SELECT /* IndexPager::buildQueryInfo (LogPager) */ log_id, log_type, log_action, log_timestamp, log_namespace, log_title, log_params, log_deleted, user_id, user_name, user_editcount, COALESCE( comment_log_comment.comment_text, log_comment ) AS `log_comment_text`, comment_log_comment.comment_data AS `log_comment_data`, comment_log_comment.comment_id AS `log_comment_cid`, log_user, log_user_text, NULL AS `log_actor`, (SELECT GROUP_CONCAT(ctd_name SEPARATOR ', ') FROM `change_tag`, `change_tag_def` WHERE (ct_log_id=log_id) AND (ct_tag_id=ctd_id) ) AS `ts_tags` FROM `logging` LEFT JOIN `comment` `comment_log_comment` ON ((comment_log_comment.comment_id = log_comment_id)) LEFT JOIN `user` ON ((user_id=log_user)) WHERE (log_action != 'revision') AND (log_action != 'flow-delete-post') AND (log_action != 'flow-delete-topic') AND (log_action != 'flow-restore-post') AND (log_action != 'flow-restore-topic') AND (log_type NOT IN ('spamblacklist', 'titleblacklist', 'abusefilterprivatedetails', 'suppress')) AND log_type IN ('delete', 'move', 'protect') AND log_namespace = '0' AND log_title = 'Q56399360' AND ((log_deleted & 1) = 0) ORDER BY log_timestamp DESC LIMIT 11;

but MySQL is pretty dumb in optimizing subqeuries (SELECT GROUP_CONCAT(ctd_name SEPARATOR ', ') FROM change_tag, change_tag_def WHERE (ct_log_id=log_id) AND (ct_tag_id=ctd_id) ) So I changed it to SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM change_tag INNER JOIN change_tag_def ON ((ct_tag_id=ctd_id))  WHERE ct_rc_id=log_id  which is pretty performant (Ran it on prod, works fine)

TASK DETAIL

https://phabricator.wikimedia.org/T207313

EMAIL PREFERENCES

https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Addshore, Ladsgroup
Cc: Lea_Lacroix_WMDE, Stashbot, Ladsgroup, gerritbot, TerraCodes, Liuxinyu970226, Lydia_Pintscher, Addshore, WMDE-leszek, Sjoerddebruin, Nikki, Krinkle, HakanIST, ValterVB, Pamputt, Mahir256, Aklapper, CucyNoiD, Nandana, NebulousIris, Gaboe420, Versusxo, Majesticalreaper22, Giuliamocci, Adrian1985, Cpaulf30, Lahi, Gq86, Baloch007, Darkminds3113, Bsandipan, Lordiis, GoranSMilovanovic, Adik2382, Th3d3v1ls, Ramalepe, Liugev6, QZanden, LawExplorer, Lewizho99, Maathavan, Jonas, Wikidata-bugs, aude, Jdforrester-WMF, Mbch331, Jay8g, Krenair

_______________________________________________
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs