Page MenuHomePhabricator
Create Task
Maniphest T68111

watchlist needs a surrogate primary key
Closed, DuplicatePublic
Actions

Description

CREATE TABLE watchlist (

wl_user int(5) unsigned NOT NULL DEFAULT '0',
wl_namespace int(11) NOT NULL DEFAULT '0',
wl_title varbinary(255) NOT NULL DEFAULT '',
wl_notificationtimestamp varbinary(14) DEFAULT NULL,
UNIQUE KEY wl_user (wl_user,wl_namespace,wl_title),
KEY namespace_title (wl_namespace,wl_title)

) ENGINE=InnoDB DEFAULT CHARSET=binary;

InnoDB uses the unique key wl_user as a primary key. Since PK is clustered, secondary indexes contain hidden copies of the PK columns, potentially making them quite large.

Bug 66089 adds another secondary index, wl_user_notificationtimestamp, so it's time to consider adding a surrogate primary key wl_id.

Version: 1.24rc
Severity: normal

Details

Reference
bz66111

Related Objects
Search...

StatusSubtypeAssignedTask
 ResolvedReedyT157227 MediaWiki DB tables with columns which references other columns but have different type (tracking)
DuplicateNoneT68111 watchlist needs a surrogate primary key
 ResolvedLSobanskiT17441 Some tables lack unique or primary keys, may allow confusing duplicate data
 ResolvedNoneT35638 Math table doesn't have a PK
 DeclinedNoneT104314 Every Gather table should have a primary key
 ResolveddanielT102992 [Task] Review WikidataQuality DB schema
 ResolvedmatthiasmullieT109676 Add artificial primary key to flow_wiki_ref and flow_ext_ref
 ResolvedjcrespoT111084 Do backfill and final schema change for "Segregate Reference objects by source wiki."
 DuplicateNoneT113778 Run patch-reference_wiki-phase5.sql in production
 ResolvedjcrespoT117787 Make ref_id of flow_wiki_ref and flow_ext_ref not null and PK (DBA phase 2)
 ResolvedCatropeT117786 Run maintenance/FlowPopulateRefId.php on all production wikis
 ResolvedCatropeT117785 Deploy artificial primary key reference change to all production wikis simultaneously
 ResolvedjcrespoT117783 Add ref_id to flow_wiki_ref and flow_ext_ref (DBA phase 1)
 ResolvedTTOT123225 change_tag doesn't have a primary key
 ResolvedMarosteguiT147166 Apply change_tag and tag_summary primary key schema change to Wikimedia wikis
 ResolvedMarosteguiT130067 Add wl_id to watchlist tables on production dbs
 ResolvedjcrespoT138810 Set databases as read-only or switchover to secondary datacenter
 ResolvedAddshoreT125990 Add a wl_id field to the watchlist table
 ResolvedAddshoreT160062 Allow setting the watchlist table to read-only on a per-wiki basis
 StalledNoneT146571 Add a primary key to querycache table
 OpenNoneT174513 Maintenance script to cleanup querycache
 OpenNoneT146585 Add a primary key to user_newtalk
 OpenNoneT146586 Add a primary key to querycachetwo table
 ResolvedMarosteguiT146591 Add a primary key to l10n_cache
 ResolvedMarosteguiT150306 truncate l10n_cache table on WMF wikis
 ResolvedReedyT146570 Give user_properties a primary key
 ResolvedReedyT172514 Add new PKs to tables.sql (mysql and sqlite)
Restricted Task
 ResolvedMarosteguiT149819 Add primary keys to remaining Flow tables
 OpenNoneT28741 Migrate file tables to a modern layout (image/oldimage; file/file_revision; add primary keys)
 ResolvedKrinkleT589 RFC: image and oldimage tables
 ResolvedMarosteguiT163912 Convert unique keys into primary keys for some wiki tables on s3-eqiad and s3-codfw
 ResolvedMarosteguiT163979 Convert unique keys into primary keys for some wiki tables on s6-eqiad and codfw
 ResolvedMarosteguiT164185 Convert unique keys into primary keys for some wiki tables on s1, s2, s4, s5 and s7 (eqiad)
 ResolvedMarosteguiT166204 Convert unique keys into primary keys for some wiki tables on s1
 ResolvedMarosteguiT166205 Convert unique keys into primary keys for some wiki tables on s2
 ResolvedMarosteguiT166206 Convert unique keys into primary keys for some wiki tables on s4
 ResolvedjcrespoT166207 Convert unique keys into primary keys for some wiki tables on s5
 ResolvedMarosteguiT166208 Convert unique keys into primary keys for some wiki tables on s7
 ResolvedMarosteguiT164399 Convert unique keys into primary keys for some wiki tables on all s* shards (codfw)
 ResolvedReedyT198811 Convert UNIQUE indexes to Primary Keys in MW Core Tables
 ResolvedMarosteguiT199368 Convert UNIQUE INDEX to PK in Production
 ResolvedSep 9 2018MarosteguiT189101 Deploy schema change for adding numeric primary key to wbqc_constraints table
 ResolvedMarosteguiT201011 Apply schema change to translate_reviews in WMF
 DuplicateNoneT231946 user_newtalk table is missing a primary key
 ResolvedDannyS712T243073 Add primary key to pagetriage_page_tags
 ResolvedUmherirrenderT243985 Translate table `revtag` lacks a primary key
 ResolvedDannyS712T243986 CloseWikis table `closedwikis` lacks a primary key
 ResolvedDannyS712T243987 GlobalUsage table `globalimagelinks` lacks a primary key
 In ProgressDiesel_kapasuleT312247 Add primary key for database table cx_suggestions
 OpenNoneT312263 Add primary key for database table translate_tmf
 OpenNoneT312281 Add primary key for database table securepoll_lists

Event Timeline

• bzimport raised the priority of this task from to Medium.Nov 22 2014, 3:11 AM
• bzimport added a project: MediaWiki-libs-Rdbms.
• bzimport set Reference to bz66111.
• bzimport added a subscriber: Unknown Object (MLST).
• Springle created this task.Jun 4 2014, 5:05 AM
• Springle added a comment.Jun 4 2014, 5:08 AM

This may have some effect on MariaDB watchlist query execution plans such as different use of extended keys or Index Condition Pushdown. Testing is in progress on an enwiki slave.

Reedy subscribed.Jan 19 2015, 9:33 PM

@Springle How did the testing go? ;)

Reedy added a subtask: T17441: Some tables lack unique or primary keys, may allow confusing duplicate data.Jan 19 2015, 9:35 PM
jcrespo closed this task as a duplicate of T125990: Add a wl_id field to the watchlist table.Mar 1 2016, 7:43 AM
LSobanski closed subtask T17441: Some tables lack unique or primary keys, may allow confusing duplicate data as Resolved.May 17 2021, 11:09 AM
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