Page MenuHomePhabricator

watchlist needs a surrogate primary key
Closed, DuplicatePublic

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

StatusSubtypeAssignedTask
ResolvedReedy
DuplicateNone
ResolvedLSobanski
ResolvedNone
DeclinedNone
Resolveddaniel
Resolvedmatthiasmullie
Resolvedjcrespo
DuplicateNone
Resolvedjcrespo
ResolvedCatrope
ResolvedCatrope
Resolvedjcrespo
ResolvedTTO
ResolvedMarostegui
ResolvedMarostegui
Resolvedjcrespo
ResolvedAddshore
ResolvedAddshore
StalledNone
OpenNone
OpenNone
OpenNone
ResolvedMarostegui
ResolvedMarostegui
ResolvedReedy
ResolvedReedy
ResolvedMarostegui
OpenNone
ResolvedKrinkle
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
Resolvedjcrespo
ResolvedMarostegui
ResolvedMarostegui
ResolvedReedy
ResolvedMarostegui
ResolvedSep 9 2018Marostegui
ResolvedMarostegui
DuplicateNone
ResolvedDannyS712
OpenNone
OpenDannyS712
ResolvedDannyS712

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 22 2014, 3:11 AM
bzimport added a project: Wikimedia-Rdbms.
bzimport set Reference to bz66111.
bzimport added a subscriber: Unknown Object (MLST).

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.