From c585ec5ed688a4359448a12529cd6b6d1b82aad0 Mon Sep 17 00:00:00 2001 From: Brad Jorsch Date: Fri, 18 Nov 2016 14:54:28 -0500 Subject: [PATCH] Add externallinks.el_index_60 column and indexes This will allow for replacing the limit-and-offset queries with queries that page in a sensible manner. Bug: T59176 Change-Id: If5c137f68496772f3fff3a735b7b0c388426e518 --- includes/installer/MssqlUpdater.php | 3 +++ includes/installer/MysqlUpdater.php | 3 +++ includes/installer/OracleUpdater.php | 3 +++ includes/installer/PostgresUpdater.php | 5 +++++ includes/installer/SqliteUpdater.php | 3 +++ .../archives/patch-externallinks-el_index_60.sql | 4 ++++ maintenance/mssql/tables.sql | 9 ++++++++- .../archives/patch-externallinks-el_index_60.sql | 5 +++++ maintenance/oracle/tables.sql | 5 ++++- maintenance/postgres/tables.sql | 11 +++++++---- maintenance/tables.sql | 9 ++++++++- 11 files changed, 53 insertions(+), 7 deletions(-) create mode 100644 maintenance/archives/patch-externallinks-el_index_60.sql create mode 100644 maintenance/oracle/archives/patch-externallinks-el_index_60.sql diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index 1175e9e7bd..968ee15dba 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -94,6 +94,9 @@ class MssqlUpdater extends DatabaseUpdater { 'patch-add-rc_name_type_patrolled_timestamp_index.sql' ], [ 'addField', 'change_tag', 'ct_id', 'patch-change_tag-ct_id.sql' ], [ 'addField', 'tag_summary', 'ts_id', 'patch-tag_summary-ts_id.sql' ], + + // 1.29 + [ 'addField', 'externallinks', 'el_index_60', 'patch-externallinks-el_index_60.sql' ], ]; } diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index a637ce01e3..d95222cbe1 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -291,6 +291,9 @@ class MysqlUpdater extends DatabaseUpdater { [ 'addField', 'change_tag', 'ct_id', 'patch-change_tag-ct_id.sql' ], [ 'addField', 'tag_summary', 'ts_id', 'patch-tag_summary-ts_id.sql' ], [ 'modifyField', 'recentchanges', 'rc_ip', 'patch-rc_ip_modify.sql' ], + + // 1.29 + [ 'addField', 'externallinks', 'el_index_60', 'patch-externallinks-el_index_60.sql' ], ]; } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index e1e0d0f82d..1f0e411af0 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -119,6 +119,9 @@ class OracleUpdater extends DatabaseUpdater { [ 'addField', 'change_tag', 'ct_id', 'patch-change_tag-ct_id.sql' ], [ 'addField', 'tag_summary', 'ts_id', 'patch-tag_summary-ts_id.sql' ], + // 1.29 + [ 'addField', 'externallinks', 'el_index_60', 'patch-externallinks-el_index_60.sql' ], + // KEEP THIS AT THE BOTTOM!! [ 'doRebuildDuplicateFunction' ], diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 790fbe7b31..1eb3f41731 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -443,6 +443,11 @@ class PostgresUpdater extends DatabaseUpdater { "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('change_tag_ct_id_seq')" ], [ 'addPgField', 'tag_summary', 'ts_id', "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('tag_summary_ts_id_seq')" ], + + // 1.29 + [ 'addPgField', 'externallinks', 'el_index_60', "BYTEA NOT NULL DEFAULT ''" ], + [ 'addPgIndex', 'externallinks', 'el_index_60', '( el_index_60, el_id )' ], + [ 'addPgIndex', 'externallinks', 'el_from_index_60', '( el_from, el_index_60, el_id )' ], ]; } diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index 388c0346ce..32068e65e8 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -158,6 +158,9 @@ class SqliteUpdater extends DatabaseUpdater { 'patch-add-rc_name_type_patrolled_timestamp_index.sql' ], [ 'addField', 'change_tag', 'ct_id', 'patch-change_tag-ct_id.sql' ], [ 'addField', 'tag_summary', 'ts_id', 'patch-tag_summary-ts_id.sql' ], + + // 1.29 + [ 'addField', 'externallinks', 'el_index_60', 'patch-externallinks-el_index_60.sql' ], ]; } diff --git a/maintenance/archives/patch-externallinks-el_index_60.sql b/maintenance/archives/patch-externallinks-el_index_60.sql new file mode 100644 index 0000000000..eacb107426 --- /dev/null +++ b/maintenance/archives/patch-externallinks-el_index_60.sql @@ -0,0 +1,4 @@ +-- @since 1.29 +ALTER TABLE /*$wgDBprefix*/externallinks ADD COLUMN el_index_60 varbinary(60) NOT NULL DEFAULT ''; +CREATE INDEX /*i*/el_index_60 ON /*_*/externallinks (el_index_60, el_id); +CREATE INDEX /*i*/el_from_index_60 ON /*_*/externallinks (el_from, el_index_60, el_id); diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 256ee364d6..ba1f752b72 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -389,11 +389,18 @@ CREATE TABLE /*_*/externallinks ( -- which allows for fast searching for all pages under example.com with the -- clause: -- WHERE el_index LIKE 'http://com.example.%' - el_index nvarchar(450) NOT NULL + el_index nvarchar(450) NOT NULL, + + -- This is el_index truncated to 60 bytes to allow for sortable queries that + -- aren't supported by a partial index. + -- @todo Drop the default once this is deployed everywhere and code is populating it. + el_index_60 varbinary(60) NOT NULL default '' ); CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from); CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index); +CREATE INDEX /*i*/el_index_60 ON /*_*/externallinks (el_index_60, el_id); +CREATE INDEX /*i*/el_from_index_60 ON /*_*/externallinks (el_from, el_index_60, el_id); -- el_to index intentionally not added; we cannot index nvarchar(max) columns, -- but we also cannot restrict el_to to a smaller column size as the external -- link may be larger. diff --git a/maintenance/oracle/archives/patch-externallinks-el_index_60.sql b/maintenance/oracle/archives/patch-externallinks-el_index_60.sql new file mode 100644 index 0000000000..c4b906d1a7 --- /dev/null +++ b/maintenance/oracle/archives/patch-externallinks-el_index_60.sql @@ -0,0 +1,5 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.externallinks ADD el_index_60 VARBINARY(60) NOT NULL DEFAULT ''; +CREATE INDEX &mw_prefix.externallinks_i04 ON &mw_prefix.externallinks (el_index_60, el_id); +CREATE INDEX &mw_prefix.externallinks_i05 ON &mw_prefix.externallinks (el_from, el_index_60, el_id); diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 616b401a26..edb3398a64 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -219,13 +219,16 @@ CREATE TABLE &mw_prefix.externallinks ( el_id NUMBER NOT NULL, el_from NUMBER NOT NULL, el_to VARCHAR2(2048) NOT NULL, - el_index VARCHAR2(2048) NOT NULL + el_index VARCHAR2(2048) NOT NULL, + el_index_60 VARBINARY(60) NOT NULL DEFAULT '' ); ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_pk PRIMARY KEY (el_id); ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_fk1 FOREIGN KEY (el_from) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.externallinks_i01 ON &mw_prefix.externallinks (el_from, el_to); CREATE INDEX &mw_prefix.externallinks_i02 ON &mw_prefix.externallinks (el_to, el_from); CREATE INDEX &mw_prefix.externallinks_i03 ON &mw_prefix.externallinks (el_index); +CREATE INDEX &mw_prefix.externallinks_i04 ON &mw_prefix.externallinks (el_index_60, el_id); +CREATE INDEX &mw_prefix.externallinks_i05 ON &mw_prefix.externallinks (el_from, el_index_60, el_id); CREATE TABLE &mw_prefix.langlinks ( ll_from NUMBER NOT NULL, diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 227376187f..61ad075b5b 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -254,13 +254,16 @@ CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from); CREATE SEQUENCE externallinks_el_id_seq; CREATE TABLE externallinks ( - el_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'), - el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - el_to TEXT NOT NULL, - el_index TEXT NOT NULL + el_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('externallinks_el_id_seq'), + el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + el_to TEXT NOT NULL, + el_index TEXT NOT NULL, + el_index_60 BYTEA NOT NULL DEFAULT '' ); CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to); CREATE INDEX externallinks_index ON externallinks (el_index); +CREATE INDEX el_index_60 ON externallinks (el_index_60, el_id); +CREATE INDEX el_from_index_60 ON externallinks (el_from, el_index_60, el_id); CREATE TABLE langlinks ( ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, diff --git a/maintenance/tables.sql b/maintenance/tables.sql index cf60d89bf5..2b6ea03cc6 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -676,12 +676,19 @@ CREATE TABLE /*_*/externallinks ( -- which allows for fast searching for all pages under example.com with the -- clause: -- WHERE el_index LIKE 'http://com.example.%' - el_index blob NOT NULL + el_index blob NOT NULL, + + -- This is el_index truncated to 60 bytes to allow for sortable queries that + -- aren't supported by a partial index. + -- @todo Drop the default once this is deployed everywhere and code is populating it. + el_index_60 varbinary(60) NOT NULL default '' ) /*$wgDBTableOptions*/; CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from, el_to(40)); CREATE INDEX /*i*/el_to ON /*_*/externallinks (el_to(60), el_from); CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index(60)); +CREATE INDEX /*i*/el_index_60 ON /*_*/externallinks (el_index_60, el_id); +CREATE INDEX /*i*/el_from_index_60 ON /*_*/externallinks (el_from, el_index_60, el_id); -- -- Track interlanguage links -- 2.20.1