Page MenuHomePhabricator
Paste P42842

(An Untitled Masterwork)
ActivePublic
Actions

Authored by Marostegui on Jan 5 2023, 9:51 AM.
Project Tags
None
Referenced Files
F35990678: raw-paste-data.txt
Jan 5 2023, 9:51 AM
Subscribers
None
Query:
SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,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`,page_latest,(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`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM `recentchanges` STRAIGHT_JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) JOIN `categorylinks` ON ((rc_cur_id = cl_from)) WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_timestamp >= '20221229093755') AND cl_to = 'Articles_with_VIAF_identifiers' ORDER BY rc_timestamp DESC LIMIT 50 ;
Tables:
root@db1176.eqiad.wmnet[enwiki]> show create table categorylinks\G show create table recentchanges\G show create table actor\G show create table page\G show create table flaggedpages\G show create table ores_model\G show create table comment\G show create table change_tag\G show create table change_tag_def\G
*************************** 1. row ***************************
Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
`cl_from` int(8) unsigned NOT NULL DEFAULT 0,
`cl_to` varbinary(255) NOT NULL DEFAULT '',
`cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
`cl_timestamp` timestamp /* mariadb-5.3 */ NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
`cl_collation` varbinary(32) NOT NULL DEFAULT '',
`cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
PRIMARY KEY (`cl_from`,`cl_to`),
KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.001 sec)
*************************** 1. row ***************************
Table: recentchanges
Create Table: CREATE TABLE `recentchanges` (
`rc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`rc_timestamp` binary(14) NOT NULL,
`rc_actor` bigint(20) unsigned NOT NULL,
`rc_namespace` int(11) NOT NULL DEFAULT 0,
`rc_title` varbinary(255) NOT NULL DEFAULT '',
`rc_comment_id` bigint(20) unsigned NOT NULL,
`rc_minor` tinyint(3) unsigned NOT NULL DEFAULT 0,
`rc_bot` tinyint(3) unsigned NOT NULL DEFAULT 0,
`rc_new` tinyint(3) unsigned NOT NULL DEFAULT 0,
`rc_cur_id` int(10) unsigned NOT NULL DEFAULT 0,
`rc_this_oldid` int(10) unsigned NOT NULL DEFAULT 0,
`rc_last_oldid` int(10) unsigned NOT NULL DEFAULT 0,
`rc_type` tinyint(3) unsigned NOT NULL DEFAULT 0,
`rc_source` varbinary(16) NOT NULL DEFAULT '',
`rc_patrolled` tinyint(3) unsigned NOT NULL DEFAULT 0,
`rc_ip` varbinary(40) NOT NULL DEFAULT '',
`rc_old_len` int(10) DEFAULT NULL,
`rc_new_len` int(10) DEFAULT NULL,
`rc_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
`rc_logid` int(10) unsigned NOT NULL DEFAULT 0,
`rc_log_type` varbinary(255) DEFAULT NULL,
`rc_log_action` varbinary(255) DEFAULT NULL,
`rc_params` blob DEFAULT NULL,
PRIMARY KEY (`rc_id`),
KEY `rc_timestamp` (`rc_timestamp`),
KEY `rc_cur_id` (`rc_cur_id`),
KEY `rc_ip` (`rc_ip`),
KEY `rc_name_type_patrolled_timestamp` (`rc_namespace`,`rc_type`,`rc_patrolled`,`rc_timestamp`),
KEY `rc_ns_actor` (`rc_namespace`,`rc_actor`),
KEY `rc_actor` (`rc_actor`,`rc_timestamp`),
KEY `rc_namespace_title_timestamp` (`rc_namespace`,`rc_title`,`rc_timestamp`),
KEY `rc_this_oldid` (`rc_this_oldid`),
KEY `rc_new_name_timestamp` (`rc_new`,`rc_namespace`,`rc_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=1587532939 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.001 sec)
*************************** 1. row ***************************
Table: actor
Create Table: CREATE TABLE `actor` (
`actor_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`actor_user` int(10) unsigned DEFAULT NULL,
`actor_name` varbinary(255) NOT NULL,
PRIMARY KEY (`actor_id`),
UNIQUE KEY `actor_name` (`actor_name`),
UNIQUE KEY `actor_user` (`actor_user`)
) ENGINE=InnoDB AUTO_INCREMENT=222987468 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.001 sec)
*************************** 1. row ***************************
Table: page
Create Table: CREATE TABLE `page` (
`page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`page_namespace` int(11) NOT NULL DEFAULT 0,
`page_title` varbinary(255) NOT NULL DEFAULT '',
`page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT 0,
`page_is_new` tinyint(1) unsigned NOT NULL DEFAULT 0,
`page_random` double unsigned NOT NULL DEFAULT 0,
`page_touched` binary(14) NOT NULL,
`page_links_updated` varbinary(14) DEFAULT NULL,
`page_latest` int(8) unsigned NOT NULL DEFAULT 0,
`page_len` int(8) unsigned NOT NULL DEFAULT 0,
`page_content_model` varbinary(32) DEFAULT NULL,
`page_lang` varbinary(35) DEFAULT NULL,
PRIMARY KEY (`page_id`),
UNIQUE KEY `page_name_title` (`page_namespace`,`page_title`),
KEY `page_random` (`page_random`),
KEY `page_len` (`page_len`),
KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
) ENGINE=InnoDB AUTO_INCREMENT=72670555 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.001 sec)
*************************** 1. row ***************************
Table: flaggedpages
Create Table: CREATE TABLE `flaggedpages` (
`fp_page_id` int(10) unsigned NOT NULL,
`fp_reviewed` tinyint(1) NOT NULL DEFAULT 0,
`fp_pending_since` binary(14) DEFAULT NULL,
`fp_stable` int(10) unsigned NOT NULL,
`fp_quality` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`fp_page_id`),
KEY `fp_reviewed_page` (`fp_reviewed`,`fp_page_id`),
KEY `fp_quality_page` (`fp_quality`,`fp_page_id`),
KEY `fp_pending_since` (`fp_pending_since`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.001 sec)
*************************** 1. row ***************************
Table: ores_model
Create Table: CREATE TABLE `ores_model` (
`oresm_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`oresm_name` varbinary(32) NOT NULL,
`oresm_version` varbinary(32) NOT NULL,
`oresm_is_current` tinyint(1) NOT NULL,
PRIMARY KEY (`oresm_id`),
UNIQUE KEY `oresm_version` (`oresm_name`,`oresm_version`),
KEY `ores_model_status` (`oresm_name`,`oresm_is_current`)
) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.001 sec)
*************************** 1. row ***************************
Table: comment
Create Table: CREATE TABLE `comment` (
`comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_hash` int(11) NOT NULL,
`comment_text` blob NOT NULL,
`comment_data` blob DEFAULT NULL,
PRIMARY KEY (`comment_id`),
KEY `comment_hash` (`comment_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=414186490 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.001 sec)
*************************** 1. row ***************************
Table: change_tag
Create Table: CREATE TABLE `change_tag` (
`ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ct_rc_id` int(10) unsigned DEFAULT NULL,
`ct_log_id` int(10) unsigned DEFAULT NULL,
`ct_rev_id` int(10) unsigned DEFAULT NULL,
`ct_params` blob DEFAULT NULL,
`ct_tag_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`ct_id`),
UNIQUE KEY `ct_rc_tag_id` (`ct_rc_id`,`ct_tag_id`),
UNIQUE KEY `ct_log_tag_id` (`ct_log_id`,`ct_tag_id`),
UNIQUE KEY `ct_rev_tag_id` (`ct_rev_id`,`ct_tag_id`),
KEY `ct_tag_id_id` (`ct_tag_id`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=294706137 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.001 sec)
*************************** 1. row ***************************
Table: change_tag_def
Create Table: CREATE TABLE `change_tag_def` (
`ctd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ctd_name` varbinary(255) NOT NULL,
`ctd_user_defined` tinyint(1) NOT NULL,
`ctd_count` bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`ctd_id`),
UNIQUE KEY `ctd_name` (`ctd_name`),
KEY `ctd_count` (`ctd_count`),
KEY `ctd_user_defined` (`ctd_user_defined`)
) ENGINE=InnoDB AUTO_INCREMENT=640 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
Query plans and query times:
10.4 (db1134)
Query time: 50 rows in set (24.554 sec)
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
| 1 | PRIMARY | categorylinks | ref | PRIMARY,cl_timestamp,cl_sortkey | cl_timestamp | 257 | const | 1605914 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_cur_id,rc_actor | rc_cur_id | 4 | enwiki.categorylinks.cl_from | 1 | Using where |
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | enwiki.recentchanges.rc_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
10 rows in set (0.002 sec)
10.6 (db1132)
Query time: 50 rows in set (23.573 sec)
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
| 1 | PRIMARY | categorylinks | ref | PRIMARY,cl_timestamp,cl_sortkey | cl_timestamp | 257 | const | 1639616 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_cur_id,rc_actor | rc_cur_id | 4 | enwiki.categorylinks.cl_from | 3 | Using where |
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | enwiki.recentchanges.rc_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
10 rows in set (0.003 sec)
11.0 (db1176)
Query time:
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
| 1 | PRIMARY | categorylinks | ref | PRIMARY,cl_timestamp,cl_sortkey | cl_timestamp | 257 | const | 1822624 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_cur_id,rc_actor | rc_cur_id | 4 | enwiki.categorylinks.cl_from | 1 | Using where |
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.categorylinks.cl_from | 1 | |
| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | enwiki.recentchanges.rc_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
+------+--------------------+---------------------+--------+---------------------------------+-----------------------+---------+------------------------------------------------+---------+-----------------------------------------------------------+
10 rows in set (0.002 sec)
Query time: 50 rows in set (10.772 sec)
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits