Change index on rc_namespace, rc_title to rc_namespace, rc_title, rc_timestamp

Bug: T190444
Change-Id: I5c59f1ee9306710505654a8db965f85ebf855873

diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php
index 2d245a7..2e33999 100644 (file)
--- 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 73a9689..60bb69f 100644 (file)
--- 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 ab349f7..737b172 100644 (file)
--- 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 c829d51..ba00dec 100644 (file)
--- 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 309f30f..b107fd1 100644 (file)
--- 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 (file)
index 0000000..11794e8
--- /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 a34b5b8..39a80e7 100644 (file)
--- 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 (file)
index 0000000..e24082b
--- /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 e69c79b..8d297a7 100644 (file)
--- 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 a361b8e..271071b 100644 (file)
--- 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 2d0c9ee..f6c55fc 100644 (file)
--- 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 (file)
index 0000000..3684066
--- /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 f601bfc..1da651a 100644 (file)
--- 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);