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

StatusAssignedTask
InvalidNone
OpenReedy
DuplicateNone
OpenNone
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
ResolvedMarostegui
ResolvedMarostegui
DuplicateNone

Event Timeline

bzimport raised the priority of this task from to Normal.Nov 22 2014, 3:11 AM
bzimport added a project: Wikimedia-Rdbms.
bzimport set Reference to bz66111.
bzimport added a subscriber: Unknown Object (MLST).
Springle created this task.Jun 4 2014, 5:05 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 added a subscriber: Reedy.Jan 19 2015, 9:33 PM

@Springle How did the testing go? ;)