Bug #56545 - Speed up topic move operation by adding an index for topic_id on the topics track table.

git-svn-id: file:///svn/phpbb/branches/phpBB-3_0_0@10462 89ea8834-ac86-4346-8a33-228a782c2dd0
author: Andreas Fischer <bantu@phpbb.com> 2010-01-28 23:15:25 +0000
committer: Andreas Fischer <bantu@phpbb.com> 2010-01-28 23:15:25 +0000
commit: 2906a24122535856f5a195264dc1d9a6836e0bcd (patch)
tree: 708b62bbacdc62a53cfce34c6909be3e39ae77b3
parent: c0cd713f4ca58d064edb70e3c2cb6d1ab6a1b042 (diff)
download: forums-2906a24122535856f5a195264dc1d9a6836e0bcd.tar
forums-2906a24122535856f5a195264dc1d9a6836e0bcd.tar.gz
forums-2906a24122535856f5a195264dc1d9a6836e0bcd.tar.bz2
forums-2906a24122535856f5a195264dc1d9a6836e0bcd.tar.xz
forums-2906a24122535856f5a195264dc1d9a6836e0bcd.zip
11 files changed, 18 insertions, 0 deletions
diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php
index a612a318a7..03932bcc13 100644
--- a/phpBB/develop/create_schema_files.php
+++ b/phpBB/develop/create_schema_files.php
@@ -1746,6 +1746,7 @@ function get_schema_struct()
 		),
 		'PRIMARY_KEY'	=> array('user_id', 'topic_id'),
 		'KEYS'			=> array(
+			'topic_id'			=> array('INDEX', 'topic_id'),
 			'forum_id'			=> array('INDEX', 'forum_id'),
 		),
 	);
diff --git a/phpBB/develop/mysql_upgrader.php b/phpBB/develop/mysql_upgrader.php
index ae29b536bc..57230339e8 100644
--- a/phpBB/develop/mysql_upgrader.php
+++ b/phpBB/develop/mysql_upgrader.php
@@ -1236,6 +1236,7 @@ function get_schema_struct()
 		),
 		'PRIMARY_KEY'	=> array('user_id', 'topic_id'),
 		'KEYS'			=> array(
+			'topic_id'			=> array('INDEX', 'topic_id'),
 			'forum_id'			=> array('INDEX', 'forum_id'),
 		),
 	);
diff --git a/phpBB/docs/CHANGELOG.html b/phpBB/docs/CHANGELOG.html
index ef432e72b3..efcb31bfe0 100644
--- a/phpBB/docs/CHANGELOG.html
+++ b/phpBB/docs/CHANGELOG.html
@@ -169,6 +169,7 @@
 		<li>[Change] Disallow deleting the last question of the Q&amp;A CAPTCHA.</li>
 		<li>[Change] Tweak Q&amp;A CAPTCHA garbage collection.</li>
 		<li>[Change] Show a proper preview for the Q&amp;A CAPTCHA. (Bug #56365)</li>
+		<li>[Change] Speed up topic move operation by adding an index for topic_id on the topics track table. (Bug #56545)</li>
 		<li>[Feature] Ability to use HTTP authentication in ATOM feeds by passing the GET parameter &quot;auth=http&quot;.</li>
 	</ul>
 
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php
index c338df0b80..c7205de89d 100644
--- a/phpBB/install/database_update.php
+++ b/phpBB/install/database_update.php
@@ -900,6 +900,11 @@ function database_update_info()
 			'drop_keys'		=> array(
 				LOG_TABLE			=> array('log_time'),
 			),
+			'add_index'		=> array(
+				TOPICS_TRACK_TABLE	=> array(
+					'topic_id'		=> array('topic_id'),
+				),
+			),
 		),
 	);
 }
diff --git a/phpBB/install/schemas/firebird_schema.sql b/phpBB/install/schemas/firebird_schema.sql
index 16965e492b..85f86781de 100644
--- a/phpBB/install/schemas/firebird_schema.sql
+++ b/phpBB/install/schemas/firebird_schema.sql
@@ -1262,6 +1262,7 @@ CREATE TABLE phpbb_topics_track (
 
 ALTER TABLE phpbb_topics_track ADD PRIMARY KEY (user_id, topic_id);;
 
+CREATE INDEX phpbb_topics_track_topic_id ON phpbb_topics_track(topic_id);;
 CREATE INDEX phpbb_topics_track_forum_id ON phpbb_topics_track(forum_id);;
 
 # Table: 'phpbb_topics_posted'
diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index 799d596e1b..960c6eeaed 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -1506,6 +1506,9 @@ ALTER TABLE [phpbb_topics_track] WITH NOCHECK ADD
 	)  ON [PRIMARY] 
 GO
 
+CREATE  INDEX [topic_id] ON [phpbb_topics_track]([topic_id]) ON [PRIMARY]
+GO
+
 CREATE  INDEX [forum_id] ON [phpbb_topics_track]([forum_id]) ON [PRIMARY]
 GO
 
diff --git a/phpBB/install/schemas/mysql_40_schema.sql b/phpBB/install/schemas/mysql_40_schema.sql
index 683f58f2e8..19b1b4f0f7 100644
--- a/phpBB/install/schemas/mysql_40_schema.sql
+++ b/phpBB/install/schemas/mysql_40_schema.sql
@@ -861,6 +861,7 @@ CREATE TABLE phpbb_topics_track (
 	forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
 	mark_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
 	PRIMARY KEY (user_id, topic_id),
+	KEY topic_id (topic_id),
 	KEY forum_id (forum_id)
 );
 
diff --git a/phpBB/install/schemas/mysql_41_schema.sql b/phpBB/install/schemas/mysql_41_schema.sql
index db80ffdf09..3b70630a9e 100644
--- a/phpBB/install/schemas/mysql_41_schema.sql
+++ b/phpBB/install/schemas/mysql_41_schema.sql
@@ -861,6 +861,7 @@ CREATE TABLE phpbb_topics_track (
 	forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
 	mark_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
 	PRIMARY KEY (user_id, topic_id),
+	KEY topic_id (topic_id),
 	KEY forum_id (forum_id)
 ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
 
diff --git a/phpBB/install/schemas/oracle_schema.sql b/phpBB/install/schemas/oracle_schema.sql
index d34cd714b7..d577fce46c 100644
--- a/phpBB/install/schemas/oracle_schema.sql
+++ b/phpBB/install/schemas/oracle_schema.sql
@@ -1665,6 +1665,8 @@ CREATE TABLE phpbb_topics_track (
 )
 /
 
+CREATE INDEX phpbb_topics_track_topic_id ON phpbb_topics_track (topic_id)
+/
 CREATE INDEX phpbb_topics_track_forum_id ON phpbb_topics_track (forum_id)
 /
 
diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql
index 6e63b3b936..50b3979adb 100644
--- a/phpBB/install/schemas/postgres_schema.sql
+++ b/phpBB/install/schemas/postgres_schema.sql
@@ -1116,6 +1116,7 @@ CREATE TABLE phpbb_topics_track (
 	PRIMARY KEY (user_id, topic_id)
 );
 
+CREATE INDEX phpbb_topics_track_topic_id ON phpbb_topics_track (topic_id);
 CREATE INDEX phpbb_topics_track_forum_id ON phpbb_topics_track (forum_id);
 
 /*
diff --git a/phpBB/install/schemas/sqlite_schema.sql b/phpBB/install/schemas/sqlite_schema.sql
index 0ce7e23c57..7ee821d395 100644
--- a/phpBB/install/schemas/sqlite_schema.sql
+++ b/phpBB/install/schemas/sqlite_schema.sql
@@ -833,6 +833,7 @@ CREATE TABLE phpbb_topics_track (
 	PRIMARY KEY (user_id, topic_id)
 );
 
+CREATE INDEX phpbb_topics_track_topic_id ON phpbb_topics_track (topic_id);
 CREATE INDEX phpbb_topics_track_forum_id ON phpbb_topics_track (forum_id);
 
 # Table: 'phpbb_topics_posted'
author	Andreas Fischer <bantu@phpbb.com>	2010-01-28 23:15:25 +0000
committer	Andreas Fischer <bantu@phpbb.com>	2010-01-28 23:15:25 +0000
commit	2906a24122535856f5a195264dc1d9a6836e0bcd (patch)
tree	708b62bbacdc62a53cfce34c6909be3e39ae77b3
parent	c0cd713f4ca58d064edb70e3c2cb6d1ab6a1b042 (diff)
download	forums-2906a24122535856f5a195264dc1d9a6836e0bcd.tar forums-2906a24122535856f5a195264dc1d9a6836e0bcd.tar.gz forums-2906a24122535856f5a195264dc1d9a6836e0bcd.tar.bz2 forums-2906a24122535856f5a195264dc1d9a6836e0bcd.tar.xz forums-2906a24122535856f5a195264dc1d9a6836e0bcd.zip