Marostegui has uploaded a new change for review. (
https://gerrit.wikimedia.org/r/370190 )
Change subject: tables.sql: Convert UNIQUE keys into PK
......................................................................
tables.sql: Convert UNIQUE keys into PK
DBAs have been doing a massive effort to convert UNIQUE KEYS into
PRIMARY KEY.
Having a PK is essential to do maintenance, specially on large tasks.
By not having a PK it is impossible to add it in a safe way if not done
directly on the master.
During the DC switchover we took the opportunity to get the PKs added on
the master and some months after that we have finished all the slaves.
Having a PK means that we can easily change the PK into another one if
needed in the future. The ones we chose might not be the best ones, but
will allow us to get them changed.
This patch reflects the change in tables.sql so the new wikis that get
created will get the same PKs that we have deployed across the board.
Bug: T17441
Change-Id: Id635297838938c7c5dfe65d45285a4d16d65152d
---
M maintenance/tables.sql
1 file changed, 23 insertions(+), 35 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core
refs/changes/90/370190/1
diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index 14f6932..071272e 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -178,10 +178,9 @@
CREATE TABLE /*_*/user_former_groups (
-- Key to user_id
ufg_user int unsigned NOT NULL default 0,
- ufg_group varbinary(255) NOT NULL default ''
+ ufg_group varbinary(255) NOT NULL default '',
+ PRIMARY KEY (ufg_user,ufg_group)
) /*$wgDBTableOptions*/;
-
-CREATE UNIQUE INDEX /*i*/ufg_user_group ON /*_*/user_former_groups
(ufg_user,ufg_group);
--
-- Stores notifications of user talk page changes, for the display
@@ -220,10 +219,10 @@
up_property varbinary(255) NOT NULL,
-- Property value as a string.
- up_value blob
+ up_value blob,
+ PRIMARY KEY (up_user,up_property)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/user_properties_user_property ON /*_*/user_properties
(up_user,up_property);
CREATE INDEX /*i*/user_properties_property ON /*_*/user_properties
(up_property);
--
@@ -552,7 +551,8 @@
-- and deletions may refer to different page records as time
-- goes by.
pl_namespace int NOT NULL default 0,
- pl_title varchar(255) binary NOT NULL default ''
+ pl_title varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (pl_from,pl_namespace,pl_title)
) /*$wgDBTableOptions*/;
-- Forward index, for page edit, save
@@ -579,7 +579,8 @@
-- and deletions may refer to different page records as time
-- goes by.
tl_namespace int NOT NULL default 0,
- tl_title varchar(255) binary NOT NULL default ''
+ tl_title varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (tl_from,tl_namespace,tl_title)
) /*$wgDBTableOptions*/;
-- Forward index, for page edit, save
@@ -606,11 +607,9 @@
-- Filename of target image.
-- This is also the page_title of the file's description page;
-- all such pages are in namespace 6 (NS_FILE).
- il_to varchar(255) binary NOT NULL default ''
+ il_to varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (il_from,il_to)
) /*$wgDBTableOptions*/;
-
--- Forward index, for cache invalidation on file update, etc.
-CREATE UNIQUE INDEX /*i*/il_from ON /*_*/imagelinks (il_from,il_to);
-- Reverse index, for Special:Whatlinkshere and file description page local
usage
CREATE INDEX /*i*/il_to ON /*_*/imagelinks (il_to,il_from);
@@ -661,10 +660,10 @@
-- paginate the three categories separately. This never has to be updated
-- after the page is created, since none of these page types can be moved to
-- any other.
- cl_type ENUM('page', 'subcat', 'file') NOT NULL default 'page'
+ cl_type ENUM('page', 'subcat', 'file') NOT NULL default 'page',
+ PRIMARY KEY (cl_from,cl_to)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/cl_from ON /*_*/categorylinks (cl_from,cl_to);
-- We always sort within a given category, and within a given type. FIXME:
-- Formerly this index didn't cover cl_type (since that didn't exist), so old
@@ -763,11 +762,9 @@
ll_lang varbinary(20) NOT NULL default '',
-- Title of the target, including namespace
- ll_title varchar(255) binary NOT NULL default ''
+ ll_title varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (ll_from,ll_lang)
) /*$wgDBTableOptions*/;
-
--- Forward index, for page edit, save, ApiQueryLanglinks
-CREATE UNIQUE INDEX /*i*/ll_from ON /*_*/langlinks (ll_from, ll_lang);
-- Index for ApiQueryLangbacklinks
CREATE INDEX /*i*/ll_lang ON /*_*/langlinks (ll_lang, ll_title);
@@ -784,11 +781,9 @@
iwl_prefix varbinary(20) NOT NULL default '',
-- Title of the target, including namespace
- iwl_title varchar(255) binary NOT NULL default ''
+ iwl_title varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (iwl_from,iwl_prefix,iwl_title)
) /*$wgDBTableOptions*/;
-
--- Forward index, for page edit, save, ApiQueryIWLinks
-CREATE UNIQUE INDEX /*i*/iwl_from ON /*_*/iwlinks (iwl_from, iwl_prefix,
iwl_title);
-- Index for ApiQueryIWBacklinks
CREATE INDEX /*i*/iwl_prefix_title_from ON /*_*/iwlinks (iwl_prefix,
iwl_title, iwl_from);
@@ -803,7 +798,7 @@
--
CREATE TABLE /*_*/site_stats (
-- The single row should contain 1 here.
- ss_row_id int unsigned NOT NULL,
+ ss_row_id int unsigned NOT NULL PRIMARY KEY,
-- Total number of edits performed.
ss_total_edits bigint unsigned default 0,
@@ -827,9 +822,6 @@
-- Number of images, equivalent to SELECT COUNT(*) FROM image
ss_images int default 0
) /*$wgDBTableOptions*/;
-
--- Pointless index to assuage developer superstitions
-CREATE UNIQUE INDEX /*i*/ss_row_id ON /*_*/site_stats (ss_row_id);
--
-- The internet is full of jerks, alas. Sometimes it's handy
@@ -1357,12 +1349,10 @@
-- Cache of interwiki transclusion
--
CREATE TABLE /*_*/transcache (
- tc_url varbinary(255) NOT NULL,
+ tc_url varbinary(255) NOT NULL PRIMARY KEY,
tc_contents text,
tc_time binary(14) NOT NULL
) /*$wgDBTableOptions*/;
-
-CREATE UNIQUE INDEX /*i*/tc_url_idx ON /*_*/transcache (tc_url);
CREATE TABLE /*_*/logging (
@@ -1436,9 +1426,9 @@
-- The value of the ID
ls_value varchar(255) NOT NULL,
-- Key to log_id
- ls_log_id int unsigned NOT NULL default 0
+ ls_log_id int unsigned NOT NULL default 0,
+ PRIMARY KEY (ls_field,ls_value,ls_log_id)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/ls_field_val ON /*_*/log_search
(ls_field,ls_value,ls_log_id);
CREATE INDEX /*i*/ls_log_id ON /*_*/log_search (ls_log_id);
@@ -1490,13 +1480,11 @@
CREATE TABLE /*_*/querycache_info (
-- Special page name
-- Corresponds to a qc_type value
- qci_type varbinary(32) NOT NULL default '',
+ qci_type varbinary(32) NOT NULL default '' PRIMARY KEY,
-- Timestamp of last update
qci_timestamp binary(14) NOT NULL default '19700101000000'
) /*$wgDBTableOptions*/;
-
-CREATE UNIQUE INDEX /*i*/qci_type ON /*_*/querycache_info (qci_type);
-- For each redirect, this table contains exactly one row defining its target
@@ -1662,9 +1650,9 @@
-- Module context vary (includes skin and language; called "md_skin" for
legacy reasons)
md_skin varbinary(32) NOT NULL,
-- JSON blob with file dependencies
- md_deps mediumblob NOT NULL
+ md_deps mediumblob NOT NULL,
+ PRIMARY KEY (md_module,md_skin)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/md_module_skin ON /*_*/module_deps (md_module,
md_skin);
-- Holds all the sites known to the wiki.
CREATE TABLE /*_*/sites (
--
To view, visit https://gerrit.wikimedia.org/r/370190
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Id635297838938c7c5dfe65d45285a4d16d65152d
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: Marostegui <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
