Page MenuHomePhabricator

Remove oresc_rev index
Closed, ResolvedPublic

Description

oresc_rev is redundant due to existence of oresc_winner: See https://github.com/wikimedia/mediawiki-extensions-ORES/blob/0de8ab13c0c683ff852257441754eca348ab0b1d/sql/ores_classification.sql#L21

I can remove it easily for future cases but we need someone to delete old indexes from nlwiki, fawiki, trwiki, wikidatawiki, ptwiki, ruwiki (6 dbs)

Details

Related Gerrit Patches:
mediawiki/extensions/ORES : masterDrop oresc_rev index in favor of oresc_winner

Event Timeline

Restricted Application added subscribers: Zppix, Aklapper. · View Herald TranscriptJul 19 2016, 6:51 PM

Change 299827 had a related patch set uploaded (by Ladsgroup):
Drop oresc_rev index in favor of oresc_winner

https://gerrit.wikimedia.org/r/299827

Change 299827 merged by jenkins-bot:
Drop oresc_rev index in favor of oresc_winner

https://gerrit.wikimedia.org/r/299827

Gehel triaged this task as Medium priority.Jul 20 2016, 9:43 AM

Here's one of queries:

mysql> explain SELECT /* SpecialRecentChanges::doMainQuery Someone */  rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_comment,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,wl_user,wl_notificationtimestamp,page_latest,(SELECT  GROUP_CONCAT(ct_tag SEPARATOR ',')  FROM `change_tag`   WHERE ct_rc_id=rc_id  ) AS `ts_tags`,oresc_probability,'0.4' AS ores_threshold  FROM `recentchanges` LEFT JOIN `watchlist` ON (wl_user = '4' AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) INNER JOIN `ores_classification` ON ((rc_this_oldid = oresc_rev AND oresc_class = 1)) LEFT JOIN `ores_model` ON ((oresc_model = oresm_id AND oresm_is_current = 1))  WHERE rc_bot = '0' AND (rc_timestamp >= '20160713000000') AND ((oresm_name = 'damaging' OR oresm_name IS NULL)) AND (oresc_probability > '0.4') AND rc_patrolled = '0' AND rc_new IN ('0','1')   ORDER BY rc_timestamp DESC LIMIT 50/g;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '/g' at line 1
mysql> explain SELECT /* SpecialRecentChanges::doMainQuery Someone */  rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_comment,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,wl_user,wl_notificationtimestamp,page_latest,(SELECT  GROUP_CONCAT(ct_tag SEPARATOR ',')  FROM `change_tag`   WHERE ct_rc_id=rc_id  ) AS `ts_tags`,oresc_probability,'0.4' AS ores_threshold  FROM `recentchanges` LEFT JOIN `watchlist` ON (wl_user = '4' AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) INNER JOIN `ores_classification` ON ((rc_this_oldid = oresc_rev AND oresc_class = 1)) LEFT JOIN `ores_model` ON ((oresc_model = oresm_id AND oresm_is_current = 1))  WHERE rc_bot = '0' AND (rc_timestamp >= '20160713000000') AND ((oresm_name = 'damaging' OR oresm_name IS NULL)) AND (oresc_probability > '0.4') AND rc_patrolled = '0' AND rc_new IN ('0','1')   ORDER BY rc_timestamp DESC LIMIT 50\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: recentchanges
         type: range
possible_keys: rc_timestamp,new_name_timestamp
          key: rc_timestamp
      key_len: 16
          ref: NULL
         rows: 248
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: watchlist
         type: eq_ref
possible_keys: wl_user,namespace_title,wl_user_notificationtimestamp
          key: wl_user
      key_len: 265
          ref: const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: ores_classification
         type: ref
possible_keys: oresc_winner
          key: oresc_winner
      key_len: 4
          ref: enwiki.recentchanges.rc_this_oldid
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: ores_model
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: enwiki.ores_classification.oresc_model
         rows: 1
        Extra: Using where
*************************** 5. row ***************************
           id: 1
  select_type: PRIMARY
        table: page
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: enwiki.recentchanges.rc_cur_id
         rows: 1
        Extra: 
*************************** 6. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: change_tag
         type: ref
possible_keys: change_tag_rc_tag
          key: change_tag_rc_tag
      key_len: 5
          ref: enwiki.recentchanges.rc_id
         rows: 1
        Extra: Using index
6 rows in set (0.01 sec)
jcrespo moved this task from Triage to In progress on the DBA board.Jul 28 2016, 10:42 AM

Mentioned in SAL [2016-07-28T11:18:22Z] <jynus> deploying schema change to all ores databases T140803

jcrespo closed this task as Resolved.Jul 28 2016, 11:48 AM
jcrespo claimed this task.

The schema change seems to have been succesful:

$ mysql -h s5-master wikidatawiki -e "SHOW CREATE TABLE ores_classification\G"
*************************** 1. row ***************************
       Table: ores_classification
Create Table: CREATE TABLE `ores_classification` (
  `oresc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `oresc_rev` int(10) unsigned NOT NULL,
  `oresc_model` smallint(6) NOT NULL,
  `oresc_class` tinyint(4) NOT NULL,
  `oresc_probability` decimal(3,3) NOT NULL,
  `oresc_is_predicted` tinyint(1) NOT NULL,
  PRIMARY KEY (`oresc_id`),
  KEY `oresc_winner` (`oresc_rev`,`oresc_is_predicted`)
) ENGINE=InnoDB AUTO_INCREMENT=4804415 DEFAULT CHARSET=binary

$ mysql -h s2-master nlwiki -e "SHOW CREATE TABLE ores_classification\G"
*************************** 1. row ***************************
       Table: ores_classification
Create Table: CREATE TABLE `ores_classification` (
  `oresc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `oresc_rev` int(10) unsigned NOT NULL,
  `oresc_model` smallint(6) NOT NULL,
  `oresc_class` tinyint(4) NOT NULL,
  `oresc_probability` decimal(3,3) NOT NULL,
  `oresc_is_predicted` tinyint(1) NOT NULL,
  PRIMARY KEY (`oresc_id`),
  KEY `oresc_winner` (`oresc_rev`,`oresc_is_predicted`)
) ENGINE=InnoDB AUTO_INCREMENT=81155 DEFAULT CHARSET=binary

$ mysql -h s7-master fawiki -e "SHOW CREATE TABLE ores_classification\G"
*************************** 1. row ***************************
       Table: ores_classification
Create Table: CREATE TABLE `ores_classification` (
  `oresc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `oresc_rev` int(10) unsigned NOT NULL,
  `oresc_model` smallint(6) NOT NULL,
  `oresc_class` tinyint(4) NOT NULL,
  `oresc_probability` decimal(3,3) NOT NULL,
  `oresc_is_predicted` tinyint(1) NOT NULL,
  PRIMARY KEY (`oresc_id`),
  KEY `oresc_winner` (`oresc_rev`,`oresc_is_predicted`)
) ENGINE=InnoDB AUTO_INCREMENT=190629 DEFAULT CHARSET=binary

$ mysql -h s2-master trwiki -e "SHOW CREATE TABLE ores_classification\G"
*************************** 1. row ***************************
       Table: ores_classification
Create Table: CREATE TABLE `ores_classification` (
  `oresc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `oresc_rev` int(10) unsigned NOT NULL,
  `oresc_model` smallint(6) NOT NULL,
  `oresc_class` tinyint(4) NOT NULL,
  `oresc_probability` decimal(3,3) NOT NULL,
  `oresc_is_predicted` tinyint(1) NOT NULL,
  PRIMARY KEY (`oresc_id`),
  KEY `oresc_winner` (`oresc_rev`,`oresc_is_predicted`)
) ENGINE=InnoDB AUTO_INCREMENT=51408 DEFAULT CHARSET=binary

$ mysql -h s2-master ptwiki -e "SHOW CREATE TABLE ores_classification\G"
*************************** 1. row ***************************
       Table: ores_classification
Create Table: CREATE TABLE `ores_classification` (
  `oresc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `oresc_rev` int(10) unsigned NOT NULL,
  `oresc_model` smallint(6) NOT NULL,
  `oresc_class` tinyint(4) NOT NULL,
  `oresc_probability` decimal(3,3) NOT NULL,
  `oresc_is_predicted` tinyint(1) NOT NULL,
  PRIMARY KEY (`oresc_id`),
  KEY `oresc_winner` (`oresc_rev`,`oresc_is_predicted`)
) ENGINE=InnoDB AUTO_INCREMENT=169991 DEFAULT CHARSET=binary

$ mysql -h s6-master ruwiki -e "SHOW CREATE TABLE ores_classification\G"
*************************** 1. row ***************************
       Table: ores_classification
Create Table: CREATE TABLE `ores_classification` (
  `oresc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `oresc_rev` int(10) unsigned NOT NULL,
  `oresc_model` smallint(6) NOT NULL,
  `oresc_class` tinyint(4) NOT NULL,
  `oresc_probability` decimal(3,3) NOT NULL,
  `oresc_is_predicted` tinyint(1) NOT NULL,
  PRIMARY KEY (`oresc_id`),
  KEY `oresc_winner` (`oresc_rev`,`oresc_is_predicted`)
) ENGINE=InnoDB AUTO_INCREMENT=351373 DEFAULT CHARSET=binary

No other database have been found with this table:

$ mysql -BN -h dbstore1002 information_schema -e "SELECT table_schema FROM tables WHERE table_name='ores_classification'"
fawiki
nlwiki
ptwiki
ruwiki
trwiki
wikidatawiki

Thank you.