Anomie moved this task from Unsorted to Needs details or plan on the
MediaWiki-API board.
Anomie edited projects, added Core Platform Team Workboards (Clinic Duty Team),
DBA; removed Core Platform Team.
Anomie added a comment.
The query in question is
SELECT
rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,rc_minor,rc_type,rc_bot,rc_old_len,rc_new_len,rc_logid,rc_log_type,rc_log_action,rc_params,(SELECT
GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def`
ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS
`ts_tags`,page_is_redirect,comment_rc_comment.comment_text AS
`rc_comment_text`,comment_rc_comment.comment_data AS
`rc_comment_data`,comment_rc_comment.comment_id AS
`rc_comment_cid`,actor_rc_user.actor_user AS `rc_user`,actor_rc_user.actor_name
AS `rc_user_text`,rc_actor FROM `recentchanges` LEFT JOIN `page` ON
((rc_namespace=page_namespace) AND (rc_title=page_title)) JOIN `comment`
`comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) JOIN
`actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE
rc_type IN (0,1,3,6) AND (page_is_redirect = 1) ORDER BY rc_timestamp
DESC,rc_id DESC LIMIT 6
It looks like it's another situation where the database decides to pick a
poor plan:
wikiadmin@10.64.16.85(wikidatawiki)> explain SELECT
rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,rc_minor,rc_type,rc_bot,rc_old_len,rc_new_len,rc_logid,rc_log_type,rc_log_action,rc_params,(SELECT
GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def`
ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS
`ts_tags`,page_is_redirect,comment_rc_comment.comment_text AS
`rc_comment_text`,comment_rc_comment.comment_data AS
`rc_comment_data`,comment_rc_comment.comment_id AS
`rc_comment_cid`,actor_rc_user.actor_user AS `rc_user`,actor_rc_user.actor_name
AS `rc_user_text`,rc_actor FROM `recentchanges` LEFT JOIN `page` ON
((rc_namespace=page_namespace) AND (rc_title=page_title)) JOIN `comment`
`comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) JOIN
`actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE
rc_type IN (0,1,3,6) AND (page_is_redirect = 1) ORDER BY rc_timestamp
DESC,rc_id DESC LIMIT 6;
+------+--------------------+--------------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys
| key
| key_len | ref
| rows | Extra |
+------+--------------------+--------------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+---------+---------------------------------+
| 1 | PRIMARY | page | ref |
name_title,page_redirect_namespace_len
| page_redirect_namespace_len | 1 | const
| 5662884 | Using temporary; Using filesort |
| 1 | PRIMARY | recentchanges | ref |
rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp
| rc_namespace_title_timestamp | 261 |
wikidatawiki.page.page_namespace,wikidatawiki.page.page_title | 1 | Using
where |
| 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY
| PRIMARY
| 8 | wikidatawiki.recentchanges.rc_actor
| 1 | |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY
| PRIMARY
| 8 | wikidatawiki.recentchanges.rc_comment_id
| 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref |
change_tag_rc_tag_id,change_tag_tag_id_id
| change_tag_rc_tag_id | 5 | wikidatawiki.recentchanges.rc_id
| 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY
| PRIMARY
| 4 | wikidatawiki.change_tag.ct_tag_id
| 1 | |
+------+--------------------+--------------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------------------+---------+---------------------------------+
This particular query does much better if we ignore the
`page_redirect_namespace_len` index:
wikiadmin@10.64.48.151(wikidatawiki)> explain SELECT
rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,rc_minor,rc_type,rc_bot,rc_old_len,rc_new_len,rc_logid,rc_log_type,rc_log_action,rc_params,(SELECT
GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def`
ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS
`ts_tags`,page_is_redirect,comment_rc_comment.comment_text AS
`rc_comment_text`,comment_rc_comment.comment_data AS
`rc_comment_data`,comment_rc_comment.comment_id AS
`rc_comment_cid`,actor_rc_user.actor_user AS `rc_user`,actor_rc_user.actor_name
AS `rc_user_text`,rc_actor FROM `recentchanges` LEFT JOIN `page` ignore index
(page_redirect_namespace_len) ON ((rc_namespace=page_namespace) AND
(rc_title=page_title)) JOIN `comment` `comment_rc_comment` ON
((comment_rc_comment.comment_id = rc_comment_id)) JOIN `actor` `actor_rc_user`
ON ((actor_rc_user.actor_id = rc_actor)) WHERE rc_type IN (0,1,3,6) AND
(page_is_redirect = 1) ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 6;
+------+--------------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------+---------+-----------------------------------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys
| key
| key_len | ref
| rows | Extra |
+------+--------------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------+---------+-----------------------------------------------------------------------------+------+-------------+
| 1 | PRIMARY | recentchanges | index |
rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp
| rc_timestamp | 16 | NULL
| 6 | Using where |
| 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY
| PRIMARY
| 8 | wikidatawiki.recentchanges.rc_actor
| 1 | |
| 1 | PRIMARY | page | eq_ref | name_title
| name_title
| 261 |
wikidatawiki.recentchanges.rc_namespace,wikidatawiki.recentchanges.rc_title |
1 | Using where |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY
| PRIMARY
| 8 | wikidatawiki.recentchanges.rc_comment_id
| 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref |
change_tag_rc_tag_id,change_tag_tag_id_id
| change_tag_rc_tag_id | 5 | wikidatawiki.recentchanges.rc_id
| 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY
| PRIMARY
| 4 | wikidatawiki.change_tag.ct_tag_id
| 1 | |
+------+--------------------+--------------------+--------+------------------------------------------------------------------------------------+----------------------+---------+-----------------------------------------------------------------------------+------+-------------+
wikiadmin@10.64.48.151(wikidatawiki)> SELECT
rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,rc_minor,rc_type,rc_bot,rc_old_len,rc_new_len,rc_logid,rc_log_type,rc_log_action,rc_params,(SELECT
GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def`
ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS
`ts_tags`,page_is_redirect,comment_rc_comment.comment_text AS
`rc_comment_text`,comment_rc_comment.comment_data AS
`rc_comment_data`,comment_rc_comment.comment_id AS
`rc_comment_cid`,actor_rc_user.actor_user AS `rc_user`,actor_rc_user.actor_name
AS `rc_user_text`,rc_actor FROM `recentchanges` LEFT JOIN `page` ignore index
(page_redirect_namespace_len) ON ((rc_namespace=page_namespace) AND
(rc_title=page_title)) JOIN `comment` `comment_rc_comment` ON
((comment_rc_comment.comment_id = rc_comment_id)) JOIN `actor` `actor_rc_user`
ON ((actor_rc_user.actor_id = rc_actor)) WHERE rc_type IN (0,1,3,6) AND
(page_is_redirect = 1) ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 6;
[...]
6 rows in set (0.04 sec)
wikiadmin@10.64.48.151(wikidatawiki)> show status like 'Hand%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 2 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1735 |
| Handler_read_last | 1 |
| Handler_read_next | 6 |
| Handler_read_prev | 908 |
| Handler_read_retry | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
TASK DETAIL
https://phabricator.wikimedia.org/T245989
WORKBOARD
https://phabricator.wikimedia.org/project/board/200/
EMAIL PREFERENCES
https://phabricator.wikimedia.org/settings/panel/emailpreferences/
To: Anomie
Cc: Anomie, Aklapper, pywikibot-bugs-list, Dvorapa, Zkhalido, Iflorez,
darthmon_wmde, WDoranWMF, Viztor, DannyS712, Nandana, Wenyi, Amorymeltzer,
Lahi, Gq86, GoranSMilovanovic, QZanden, Tbscho, MayS, Marostegui, LawExplorer,
Sethakill, Minhnv-2809, Mdupont, JJMC89, dg711, _jensen, rosalieper,
Altostratus, Avicennasis, Scott_WUaS, Pchelolo, mys_721tx, Wikidata-bugs, aude,
jayvdb, Masti, Alchimista, Mbch331, Rxy, Jay8g, Krenair, Legoktm, holger.knust,
EvanProdromou, Agabi10
_______________________________________________
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
