We'll need this for https://gerrit.wikimedia.org/r/302480 and it may also help for ApiEchoUnreadNotificationPages.php (although that query is fast already).
SQL: https://gerrit.wikimedia.org/r/#/c/306862/1/db_patches/patch-add-event_page_id-index.sql
Where: all x1 databases that have an echo_event table (same set as echo.dblist)
Back compat: yes, because this is an index creation
| Subject | Repo | Branch | Lines +/- | |
|---|---|---|---|---|
| Add index on event_page_id | mediawiki/extensions/Echo | master | +3 -0 |
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | None | T143963 The Great Echo Database Cleanup of 2016 (and 2019) | |||
| Resolved | SBisson | T217452 Echo should use event_page_id for delete/undelete moderation, instead of or in addition to echo_target_page | |||
| Resolved | Marostegui | T143961 Add index on event_page_id on echo_event table |
Change 306862 had a related patch set uploaded (by Catrope):
Add index on event_page_id
The patch has been merged, but the index still needs to be added in production. I'm collecting this change and others at T143963: The Great Echo Database Cleanup of 2016 (and 2019) before sending them to the DBAs.
No it hasn't. Database changes are not deployed automatically to production.
mysql:research@s3-analytics-slave [mediawikiwiki]> SHOW CREATE TABLE echo_event; +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | echo_event | CREATE TABLE `echo_event` ( `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `event_type` varbinary(64) NOT NULL, `event_variant` varbinary(64) DEFAULT NULL, `event_agent_id` int(10) unsigned DEFAULT NULL, `event_agent_ip` varbinary(39) DEFAULT NULL, `event_page_namespace` int(10) unsigned DEFAULT NULL, `event_page_title` varbinary(255) DEFAULT NULL, `event_extra` blob, `event_page_id` int(10) unsigned DEFAULT NULL, `event_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`event_id`), KEY `event_type` (`event_type`) ) ENGINE=TokuDB AUTO_INCREMENT=461932 DEFAULT CHARSET=binary `compression`='tokudb_zlib' | +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.03 sec)
mysql:research@x1-analytics-slave [enwiki]> SHOW CREATE TABLE echo_event; +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | echo_event | CREATE TABLE `echo_event` ( `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `event_type` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `event_variant` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `event_agent_id` int(10) unsigned DEFAULT NULL, `event_agent_ip` varchar(39) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `event_page_namespace` int(10) unsigned DEFAULT NULL, `event_page_title` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `event_extra` blob, `event_page_id` int(10) unsigned DEFAULT NULL, `event_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`event_id`), KEY `event_type` (`event_type`) ) ENGINE=InnoDB AUTO_INCREMENT=87349129 DEFAULT CHARSET=latin1 | +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql:research@x1-analytics-slave [enwiki]>
x1 hosts
x1 is running ROW based, as this is only changing the indexes and not touching, it should not affect replication - I will test it though just to be safe.
Mentioned in SAL (#wikimedia-operations) [2019-04-01T08:09:05Z] <marostegui> Deploy testing schema change on enwiki.echo_event on db2033 and upgrade mysql - T143961