From f303a13a72605735b071d07aa56b79c12e614ef5 Mon Sep 17 00:00:00 2001 From: Amir Sarabadani Date: Thu, 22 Mar 2018 20:46:49 +0100 Subject: [PATCH] Change index on rc_namespace, rc_title to rc_namespace, rc_title, rc_timestamp Bug: T190444 Change-Id: I5c59f1ee9306710505654a8db965f85ebf855873 --- includes/installer/MssqlUpdater.php | 2 ++ includes/installer/MysqlUpdater.php | 2 ++ includes/installer/OracleUpdater.php | 2 ++ includes/installer/PostgresUpdater.php | 6 ++++++ includes/installer/SqliteUpdater.php | 2 ++ maintenance/archives/patch-recentchanges-nttindex.sql | 11 +++++++++++ maintenance/mssql/tables.sql | 2 +- .../oracle/archives/patch-recentchanges-nttindex.sql | 4 ++++ maintenance/oracle/tables.sql | 2 +- maintenance/postgres/tables.sql | 2 +- maintenance/sqlite/archives/initial-indexes.sql | 2 +- .../sqlite/archives/patch-recentchanges-nttindex.sql | 10 ++++++++++ maintenance/tables.sql | 2 +- 13 files changed, 44 insertions(+), 5 deletions(-) create mode 100644 maintenance/archives/patch-recentchanges-nttindex.sql create mode 100644 maintenance/oracle/archives/patch-recentchanges-nttindex.sql create mode 100644 maintenance/sqlite/archives/patch-recentchanges-nttindex.sql diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index 2d245a7281..2e339997c4 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -126,6 +126,8 @@ class MssqlUpdater extends DatabaseUpdater { [ 'modifyTable', 'site_stats', 'patch-site_stats-modify.sql' ], [ 'populateArchiveRevId' ], [ 'modifyField', 'recentchanges', 'rc_patrolled', 'patch-rc_patrolled_type.sql' ], + [ 'addIndex', 'recentchanges', 'rc_namespace_title_timestamp', + 'patch-recentchanges-nttindex.sql' ], ]; } diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 73a968972c..60bb69fdeb 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -346,6 +346,8 @@ class MysqlUpdater extends DatabaseUpdater { [ 'modifyField', 'revision', 'rev_text_id', 'patch-rev_text_id-default.sql' ], [ 'modifyTable', 'site_stats', 'patch-site_stats-modify.sql' ], [ 'populateArchiveRevId' ], + [ 'addIndex', 'recentchanges', 'rc_namespace_title_timestamp', + 'patch-recentchanges-nttindex.sql' ], ]; } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index ab349f7e1d..737b1728fa 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -145,6 +145,8 @@ class OracleUpdater extends DatabaseUpdater { [ 'migrateActors' ], [ 'modifyTable', 'site_stats', 'patch-site_stats-modify.sql' ], [ 'populateArchiveRevId' ], + [ 'addIndex', 'recentchanges', 'rc_namespace_title_timestamp', + 'patch-recentchanges-nttindex.sql' ], // KEEP THIS AT THE BOTTOM!! [ 'doRebuildDuplicateFunction' ], diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index c829d51cfc..ba00dec595 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -539,6 +539,12 @@ class PostgresUpdater extends DatabaseUpdater { [ 'migrateActors' ], [ 'modifyTable', 'site_stats', 'patch-site_stats-modify.sql' ], [ 'populateArchiveRevId' ], + [ 'dropPgIndex', 'recentchanges', 'rc_namespace_title' ], + [ + 'addPgIndex', + 'recentchanges', + 'rc_namespace_title_timestamp', '( rc_namespace, rc_title, rc_timestamp )' + ], ]; } diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index 309f30f693..b107fd11ec 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -210,6 +210,8 @@ class SqliteUpdater extends DatabaseUpdater { [ 'modifyField', 'revision', 'rev_text_id', 'patch-rev_text_id-default.sql' ], [ 'modifyTable', 'site_stats', 'patch-site_stats-modify.sql' ], [ 'populateArchiveRevId' ], + [ 'addIndex', 'recentchanges', 'rc_namespace_title_timestamp', + 'patch-recentchanges-nttindex.sql' ], ]; } diff --git a/maintenance/archives/patch-recentchanges-nttindex.sql b/maintenance/archives/patch-recentchanges-nttindex.sql new file mode 100644 index 0000000000..11794e80e6 --- /dev/null +++ b/maintenance/archives/patch-recentchanges-nttindex.sql @@ -0,0 +1,11 @@ +-- +-- patch-recentchanges-nttindex.sql +-- +-- Per task T57377 +-- +-- Improve performance API queries to ask for a certain pages +-- + + +DROP INDEX /*i*/rc_namespace_title ON /*_*/recentchanges; +CREATE INDEX /*i*/rc_namespace_title_timestamp ON /*_*/recentchanges (rc_namespace, rc_title, rc_timestamp); diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index a34b5b8a28..39a80e78ba 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -1028,7 +1028,7 @@ CREATE TABLE /*_*/recentchanges ( ); CREATE INDEX /*i*/rc_timestamp ON /*_*/recentchanges (rc_timestamp); -CREATE INDEX /*i*/rc_namespace_title ON /*_*/recentchanges (rc_namespace, rc_title); +CREATE INDEX /*i*/rc_namespace_title_timestamp ON /*_*/recentchanges (rc_namespace, rc_title, rc_timestamp); CREATE INDEX /*i*/rc_cur_id ON /*_*/recentchanges (rc_cur_id); CREATE INDEX /*i*/new_name_timestamp ON /*_*/recentchanges (rc_new,rc_namespace,rc_timestamp); CREATE INDEX /*i*/rc_ip ON /*_*/recentchanges (rc_ip); diff --git a/maintenance/oracle/archives/patch-recentchanges-nttindex.sql b/maintenance/oracle/archives/patch-recentchanges-nttindex.sql new file mode 100644 index 0000000000..e24082bf84 --- /dev/null +++ b/maintenance/oracle/archives/patch-recentchanges-nttindex.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +DROP INDEX IF EXISTS &mw_prefix.recentchanges_i02; +CREATE INDEX &mw_prefix.recentchanges_i09 ON &mw_prefix.recentchanges (rc_namespace, rc_title, rc_timestamp); diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index e69c79bf59..8d297a79d0 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -688,7 +688,7 @@ ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk1 ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk2 FOREIGN KEY (rc_cur_id) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk3 FOREIGN KEY (rc_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.recentchanges_i01 ON &mw_prefix.recentchanges (rc_timestamp); -CREATE INDEX &mw_prefix.recentchanges_i02 ON &mw_prefix.recentchanges (rc_namespace, rc_title); +CREATE INDEX &mw_prefix.recentchanges_i09 ON &mw_prefix.recentchanges (rc_namespace, rc_title, rc_timestamp); CREATE INDEX &mw_prefix.recentchanges_i03 ON &mw_prefix.recentchanges (rc_cur_id); CREATE INDEX &mw_prefix.recentchanges_i04 ON &mw_prefix.recentchanges (rc_new,rc_namespace,rc_timestamp); CREATE INDEX &mw_prefix.recentchanges_i05 ON &mw_prefix.recentchanges (rc_ip); diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index a361b8e1e6..271071b78d 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -559,7 +559,7 @@ CREATE TABLE recentchanges ( ); CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp); CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0; -CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title); +CREATE INDEX rc_namespace_title_timestamp ON recentchanges (rc_namespace, rc_title, rc_timestamp); CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id); CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp); CREATE INDEX rc_ip ON recentchanges (rc_ip); diff --git a/maintenance/sqlite/archives/initial-indexes.sql b/maintenance/sqlite/archives/initial-indexes.sql index 2d0c9eea9c..f6c55fcb42 100644 --- a/maintenance/sqlite/archives/initial-indexes.sql +++ b/maintenance/sqlite/archives/initial-indexes.sql @@ -441,7 +441,7 @@ CREATE INDEX /*i*/fa_group_key ON /*_*/filearchive (fa_storage_group, fa_storage CREATE INDEX /*i*/fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp); CREATE INDEX /*i*/fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp); CREATE INDEX /*i*/rc_timestamp ON /*_*/recentchanges (rc_timestamp); -CREATE INDEX /*i*/rc_namespace_title ON /*_*/recentchanges (rc_namespace, rc_title); +CREATE INDEX /*i*/rc_namespace_title_timestamp ON /*_*/recentchanges (rc_namespace, rc_title, rc_timestamp); CREATE INDEX /*i*/rc_cur_id ON /*_*/recentchanges (rc_cur_id); CREATE INDEX /*i*/new_name_timestamp ON /*_*/recentchanges (rc_new,rc_namespace,rc_timestamp); CREATE INDEX /*i*/rc_ip ON /*_*/recentchanges (rc_ip); diff --git a/maintenance/sqlite/archives/patch-recentchanges-nttindex.sql b/maintenance/sqlite/archives/patch-recentchanges-nttindex.sql new file mode 100644 index 0000000000..3684066846 --- /dev/null +++ b/maintenance/sqlite/archives/patch-recentchanges-nttindex.sql @@ -0,0 +1,10 @@ +-- +-- patch-recentchanges-nttindex.sql +-- +-- Per task T57377 +-- +-- Improve performance API queries to ask for a certain pages +-- + +DROP INDEX IF EXISTS /*i*/rc_namespace_title; +CREATE INDEX IF NOT EXISTS /*i*/rc_namespace_title_timestamp ON /*_*/recentchanges (rc_namespace, rc_title, rc_timestamp); diff --git a/maintenance/tables.sql b/maintenance/tables.sql index f601bfcb6b..1da651adaf 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -1464,7 +1464,7 @@ CREATE TABLE /*_*/recentchanges ( CREATE INDEX /*i*/rc_timestamp ON /*_*/recentchanges (rc_timestamp); -- Special:Watchlist -CREATE INDEX /*i*/rc_namespace_title ON /*_*/recentchanges (rc_namespace, rc_title); +CREATE INDEX /*i*/rc_namespace_title_timestamp ON /*_*/recentchanges (rc_namespace, rc_title, rc_timestamp); -- Special:Recentchangeslinked when finding changes in pages linked from a page CREATE INDEX /*i*/rc_cur_id ON /*_*/recentchanges (rc_cur_id); -- 2.20.1