From 65a304c44451d0a62d01b809f3ef24c921286702 Mon Sep 17 00:00:00 2001 From: Jackmcbarn Date: Mon, 9 Dec 2013 16:11:06 -0500 Subject: [PATCH] Add index on logging.log_user_text Fixing bug #54404 requires querying against logging.log_user_text, which is currently unindexed. Add an index, to prevent performance from being adversely affected by the fix. Bug: 54432 Change-Id: I6b2af65c75bbee57377cb1ec2e75ea41a5091f80 --- includes/installer/MysqlUpdater.php | 2 ++ includes/installer/OracleUpdater.php | 4 ++++ includes/installer/PostgresUpdater.php | 2 ++ includes/installer/SqliteUpdater.php | 2 ++ maintenance/archives/patch-logging_user_text_time_index.sql | 1 + .../archives/patch-logging_user_text_type_time_index.sql | 1 + maintenance/mssql/tables.sql | 2 ++ .../oracle/archives/patch-logging_user_text_time_index.sql | 4 ++++ .../archives/patch-logging_user_text_type_time_index.sql | 4 ++++ maintenance/oracle/tables.sql | 2 ++ maintenance/postgres/tables.sql | 2 ++ maintenance/tables.sql | 2 ++ 12 files changed, 28 insertions(+) create mode 100644 maintenance/archives/patch-logging_user_text_time_index.sql create mode 100644 maintenance/archives/patch-logging_user_text_type_time_index.sql create mode 100644 maintenance/oracle/archives/patch-logging_user_text_time_index.sql create mode 100644 maintenance/oracle/archives/patch-logging_user_text_type_time_index.sql diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 01ee060b3f..7833ec01a2 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -246,6 +246,8 @@ class MysqlUpdater extends DatabaseUpdater { // 1.23 array( 'addField', 'recentchanges', 'rc_source', 'patch-rc_source.sql' ), + array( 'addIndex', 'logging', 'log_user_text_type_time', 'patch-log_user_text_type_time_index.sql' ), + array( 'addIndex', 'logging', 'log_user_text_time', 'patch-log_user_text_time_index.sql' ), ); } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index 7b6f49c658..88e5fbd59d 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -93,6 +93,10 @@ class OracleUpdater extends DatabaseUpdater { array( 'modifyField', 'user_former_groups', 'ufg_group', 'patch-ufg_group-length-increase-255.sql' ), + //1.23 + array( 'addIndex', 'logging', 'i06', 'patch-logging_user_text_type_time_index.sql' ), + array( 'addIndex', 'logging', 'i07', 'patch-logging_user_text_time_index.sql' ), + // KEEP THIS AT THE BOTTOM!! array( 'doRebuildDuplicateFunction' ), diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 3ecb79b111..dac1400945 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -259,6 +259,8 @@ class PostgresUpdater extends DatabaseUpdater { array( 'addPgIndex', 'job', 'job_cmd_token', '(job_cmd, job_token, job_random)' ), array( 'addPgIndex', 'job', 'job_cmd_token_id', '(job_cmd, job_token, job_id)' ), array( 'addPgIndex', 'filearchive', 'fa_sha1', '(fa_sha1)' ), + array( 'addPgIndex', 'logging', 'logging_user_text_type_time', '(log_user_text, log_type, log_timestamp)' ), + array( 'addPgIndex', 'logging', 'logging_user_text_time', '(log_user_text, log_timestamp)' ), array( 'checkIndex', 'pagelink_unique', array( array( 'pl_from', 'int4_ops', 'btree', 0 ), diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index e0ed1ec5d4..8f117dfe30 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -123,6 +123,8 @@ class SqliteUpdater extends DatabaseUpdater { // 1.23 array( 'addField', 'recentchanges', 'rc_source', 'patch-rc_source.sql' ), + array( 'addIndex', 'logging', 'log_user_text_type_time', 'patch-logging_user_text_type_time_index.sql' ), + array( 'addIndex', 'logging', 'log_user_text_time', 'patch-logging_user_text_time_index.sql' ), ); } diff --git a/maintenance/archives/patch-logging_user_text_time_index.sql b/maintenance/archives/patch-logging_user_text_time_index.sql new file mode 100644 index 0000000000..06f29861f3 --- /dev/null +++ b/maintenance/archives/patch-logging_user_text_time_index.sql @@ -0,0 +1 @@ +CREATE INDEX /*i*/log_user_text_time ON /*_*/logging (log_user_text, log_timestamp); diff --git a/maintenance/archives/patch-logging_user_text_type_time_index.sql b/maintenance/archives/patch-logging_user_text_type_time_index.sql new file mode 100644 index 0000000000..2801bc86f9 --- /dev/null +++ b/maintenance/archives/patch-logging_user_text_type_time_index.sql @@ -0,0 +1 @@ +CREATE INDEX /*i*/log_user_text_type_time ON /*_*/logging (log_user_text, log_type, log_timestamp); diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 8a565d5c6f..71fdd61272 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -619,6 +619,8 @@ CREATE INDEX /*$wgDBprefix*/page_time ON /*$wgDBprefix*/logging (log_namespace, CREATE INDEX /*$wgDBprefix*/times ON /*$wgDBprefix*/logging (log_timestamp); CREATE INDEX /*$wgDBprefix*/log_user_type_time ON /*$wgDBprefix*/logging (log_user, log_type, log_timestamp); CREATE INDEX /*$wgDBprefix*/log_page_id_time ON /*$wgDBprefix*/logging (log_page,log_timestamp); +CREATE INDEX /*$wgDBprefix*/log_user_text_type_time ON /*$wgDBprefix*/logging (log_user_text, log_type, log_timestamp); +CREATE INDEX /*$wgDBprefix*/log_user_text_time ON /*$wgDBprefix*/logging (log_user_text, log_timestamp); CREATE TABLE /*$wgDBprefix*/log_search ( -- The type of ID (rev ID, log ID, rev timestamp, username) diff --git a/maintenance/oracle/archives/patch-logging_user_text_time_index.sql b/maintenance/oracle/archives/patch-logging_user_text_time_index.sql new file mode 100644 index 0000000000..e04abf5f19 --- /dev/null +++ b/maintenance/oracle/archives/patch-logging_user_text_time_index.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE INDEX &mw_prefix.logging_i07 ON &mw_prefix.logging (log_user_text, log_timestamp); + diff --git a/maintenance/oracle/archives/patch-logging_user_text_type_time_index.sql b/maintenance/oracle/archives/patch-logging_user_text_type_time_index.sql new file mode 100644 index 0000000000..c1c0d4f286 --- /dev/null +++ b/maintenance/oracle/archives/patch-logging_user_text_type_time_index.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE INDEX &mw_prefix.logging_i06 ON &mw_prefix.logging (log_user_text, log_type, log_timestamp); + diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 2395bc53a6..a6b5350cac 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -509,6 +509,8 @@ CREATE INDEX &mw_prefix.logging_i02 ON &mw_prefix.logging (log_user, log_timesta CREATE INDEX &mw_prefix.logging_i03 ON &mw_prefix.logging (log_namespace, log_title, log_timestamp); CREATE INDEX &mw_prefix.logging_i04 ON &mw_prefix.logging (log_timestamp); CREATE INDEX &mw_prefix.logging_i05 ON &mw_prefix.logging (log_type, log_action, log_timestamp); +CREATE INDEX &mw_prefix.logging_i06 ON &mw_prefix.logging (log_user_text, log_type, log_timestamp); +CREATE INDEX &mw_prefix.logging_i07 ON &mw_prefix.logging (log_user_text, log_timestamp); CREATE TABLE &mw_prefix.log_search ( ls_field VARCHAR2(32) NOT NULL, diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index d0d1e92de8..b8a71850e1 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -510,6 +510,8 @@ CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timesta CREATE INDEX logging_times ON logging (log_timestamp); CREATE INDEX logging_user_type_time ON logging (log_user, log_type, log_timestamp); CREATE INDEX logging_page_id_time ON logging (log_page, log_timestamp); +CREATE INDEX logging_user_text_type_time ON logging (log_user_text, log_type, log_timestamp); +CREATE INDEX logging_user_text_time ON logging (log_user_text, log_timestamp); CREATE TABLE log_search ( ls_field TEXT NOT NULL, diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 18139b29c5..9a93c44d47 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -1263,6 +1263,8 @@ CREATE INDEX /*i*/times ON /*_*/logging (log_timestamp); CREATE INDEX /*i*/log_user_type_time ON /*_*/logging (log_user, log_type, log_timestamp); CREATE INDEX /*i*/log_page_id_time ON /*_*/logging (log_page,log_timestamp); CREATE INDEX /*i*/type_action ON /*_*/logging (log_type, log_action, log_timestamp); +CREATE INDEX /*i*/log_user_text_type_time ON /*_*/logging (log_user_text, log_type, log_timestamp); +CREATE INDEX /*i*/log_user_text_time ON /*_*/logging (log_user_text, log_timestamp); CREATE TABLE /*_*/log_search ( -- 2.20.1