[Wikidata-bugs] [Maniphest] [Updated] T189026: IndexPager::buildQueryInfo (contributions page unfiltered) query needs tuning
Marostegui added a project: mariadb-optimizer-bug. TASK DETAIL https://phabricator.wikimedia.org/T189026 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: Anomie, Marostegui Cc: gerritbot, Marostegui, Anomie, Aklapper, jcrespo, darthmon_wmde, DannyS712, Nandana, Banyek, Rayssa-, Lahi, Gq86, GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, TerraCodes, _jensen, rosalieper, Agabi10, Wikidata-bugs, aude, Dinoguy1000, Mbch331, Jay8g, Krenair ___ Wikidata-bugs mailing list Wikidata-bugs@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
[Wikidata-bugs] [Maniphest] [Updated] T189026: IndexPager::buildQueryInfo (contributions page unfiltered) query needs tuning
matej_suchanek edited projects, added TestMe; removed Patch-For-Review. TASK DETAILhttps://phabricator.wikimedia.org/T189026EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: matej_suchanekCc: gerritbot, Marostegui, Anomie, Aklapper, jcrespo, Rayssa-, Lahi, Gq86, GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, TerraCodes, Luke081515, Wikidata-bugs, aude, Mbch331, Jay8g, Krenair, Versusxo, Majesticalreaper22, Giuliamocci, Adrian1985, Cpaulf30, Baloch007, Darkminds3113, Bsandipan, Lordiis, Adik2382, Th3d3v1ls, Ramalepe, Liugev6, Lewizho99, Maathavan___ Wikidata-bugs mailing list Wikidata-bugs@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
[Wikidata-bugs] [Maniphest] [Updated] T189026: IndexPager::buildQueryInfo (contributions page unfiltered) query needs tuning
ReleaseTaggerBot added a project: MW-1.31-release-notes (WMF-deploy-2018-03-13 (1.31.0-wmf.25)). TASK DETAILhttps://phabricator.wikimedia.org/T189026EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: ReleaseTaggerBotCc: gerritbot, Marostegui, Anomie, Aklapper, jcrespo, Giuliamocci, Adrian1985, Cpaulf30, Rayssa-, Lahi, Gq86, Baloch007, Darkminds3113, Lordiis, GoranSMilovanovic, Adik2382, Th3d3v1ls, Ramalepe, Liugev6, QZanden, LawExplorer, Lewizho99, Minhnv-2809, Maathavan, Luke081515, Wikidata-bugs, aude, Mbch331, Jay8g, Krenair___ Wikidata-bugs mailing list Wikidata-bugs@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
[Wikidata-bugs] [Maniphest] [Updated] T189026: IndexPager::buildQueryInfo (contributions page unfiltered) query needs tuning
gerritbot added a project: Patch-For-Review. TASK DETAILhttps://phabricator.wikimedia.org/T189026EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: gerritbotCc: gerritbot, Marostegui, Anomie, Aklapper, jcrespo, Giuliamocci, Adrian1985, Cpaulf30, Rayssa-, Lahi, Gq86, Baloch007, Darkminds3113, Lordiis, GoranSMilovanovic, Adik2382, Th3d3v1ls, Ramalepe, Liugev6, QZanden, LawExplorer, Lewizho99, Minhnv-2809, Maathavan, Luke081515, Wikidata-bugs, aude, Mbch331, Jay8g, Krenair___ Wikidata-bugs mailing list Wikidata-bugs@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
[Wikidata-bugs] [Maniphest] [Updated] T189026: IndexPager::buildQueryInfo (contributions page unfiltered) query needs tuning
Anomie added a comment. wikiadmin@db1101(wikidatawiki)>explain SELECT rev_id,rev_page,rev_text_id,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,COALESCE( comment_rev_comment.comment_text, rev_comment ) AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,rev_user,rev_user_text,NULL AS `rev_actor`,rev_content_format,rev_content_model,page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,user_name,page_is_new,(SELECT GROUP_CONCAT(ct_tag SEPARATOR ',') FROM `change_tag`WHERE ct_rev_id=rev_id ) AS `ts_tags`,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,0.412 AS `ores_damaging_threshold` FROM `revision` LEFT JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) LEFT JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) INNER JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON ((rev_user != 0) AND (user_id = rev_user)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = '9' AND (ores_damaging_cls.oresc_rev=rev_id) AND ores_damaging_cls.oresc_class = '1') WHERE ((rev_user = 2752938)) AND (rev_parent_id = 0) AND (page_namespace = '0') AND ((rev_deleted & 4) = 0) ORDER BY rev_timestamp DESC LIMIT 51; +--++-++---+---+-+-+--+-+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra | +--++-++---+---+-+-+--+-+ |1 | PRIMARY| page| ref| PRIMARY,name_title| name_title| 4 | const | 23650821 | Using temporary; Using filesort | |1 | PRIMARY| revision| ref| user_timestamp,rev_page_id,page_timestamp,page_user_timestamp | page_user_timestamp | 8 | wikidatawiki.page.page_id,const |1 | Using where | |1 | PRIMARY| temp_rev_comment| ref| PRIMARY,revcomment_rev| PRIMARY | 4 | wikidatawiki.revision.rev_id|1 | Using index | |1 | PRIMARY| comment_rev_comment | eq_ref | PRIMARY | PRIMARY | 8 | wikidatawiki.temp_rev_comment.revcomment_comment_id |1 | Using where | |1 | PRIMARY| user| const | PRIMARY | PRIMARY | 4 | const |1 | Using where | |1 | PRIMARY| ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | wikidatawiki.revision.rev_id,const,const|1 | | |2 | DEPENDENT SUBQUERY | change_tag | ref| change_tag_rev_tag| change_tag_rev_tag| 5 | wikidatawiki.revision.rev_id|1 | Using where; Using index| +--++-++---+---+-+-+--+-+ 7 rows in set (0.00 sec) It decides it wants to fetch all the pages in namespace 0, then use the page_user_timestamp to get all the revisions for those pages by the user in question. It's estimating 23 million rows it'll have to touch, out of 47 million total in namespace 0. It could probably do better here, by using the user_timestamp index to fetch the revisions in order and filter them: wikiadmin@db1101(wikidatawiki)>explain SELECT rev_id,rev_page,rev_text_id,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,COALESCE( comment_rev_comment.comment_text, rev_comment ) AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,rev_user,rev_user_text,NULL A
