aboutsummaryrefslogtreecommitdiffstats
path: root/phpBB
diff options
context:
space:
mode:
authorNils Adermann <naderman@naderman.de>2007-03-04 16:05:17 +0000
committerNils Adermann <naderman@naderman.de>2007-03-04 16:05:17 +0000
commitac21b7d47b4c8783a41ba4dbf8d26971c252c6d1 (patch)
tree8110ad6a167a84c7c6d0a849ec9fa645ed5b1178 /phpBB
parent567b01302b735c03e596be1b1b7d01261084c62b (diff)
downloadforums-ac21b7d47b4c8783a41ba4dbf8d26971c252c6d1.tar
forums-ac21b7d47b4c8783a41ba4dbf8d26971c252c6d1.tar.gz
forums-ac21b7d47b4c8783a41ba4dbf8d26971c252c6d1.tar.bz2
forums-ac21b7d47b4c8783a41ba4dbf8d26971c252c6d1.tar.xz
forums-ac21b7d47b4c8783a41ba4dbf8d26971c252c6d1.zip
- added a UNIQUE index on the wordmatch table
- some modifications of search indexing which might improve the speed and hopefully fixes [Bug #8352] - added logging to search indexing [Bug #8384] git-svn-id: file:///svn/phpbb/trunk@7119 89ea8834-ac86-4346-8a33-228a782c2dd0
Diffstat (limited to 'phpBB')
-rw-r--r--phpBB/develop/create_schema_files.php1
-rw-r--r--phpBB/includes/acp/acp_search.php54
-rw-r--r--phpBB/includes/functions.php6
-rwxr-xr-xphpBB/includes/search/fulltext_native.php6
-rw-r--r--phpBB/install/database_update.php5
-rw-r--r--phpBB/install/schemas/firebird_schema.sql1
-rw-r--r--phpBB/install/schemas/mssql_schema.sql3
-rw-r--r--phpBB/install/schemas/mysql_40_schema.sql1
-rw-r--r--phpBB/install/schemas/mysql_41_schema.sql1
-rw-r--r--phpBB/install/schemas/oracle_schema.sql3
-rw-r--r--phpBB/install/schemas/postgres_schema.sql1
-rw-r--r--phpBB/install/schemas/sqlite_schema.sql1
-rw-r--r--phpBB/language/en/acp/common.php2
13 files changed, 57 insertions, 28 deletions
diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php
index eede9a2bd1..264b722d8a 100644
--- a/phpBB/develop/create_schema_files.php
+++ b/phpBB/develop/create_schema_files.php
@@ -1474,6 +1474,7 @@ function get_schema_struct()
'title_match' => array('BOOL', 0),
),
'KEYS' => array(
+ 'unique_match' => array('UNIQUE', array('word_id', 'post_id', 'title_match')),
'word_id' => array('INDEX', 'word_id'),
'post_id' => array('INDEX', 'post_id'),
),
diff --git a/phpBB/includes/acp/acp_search.php b/phpBB/includes/acp/acp_search.php
index ab81721311..2353150c03 100644
--- a/phpBB/includes/acp/acp_search.php
+++ b/phpBB/includes/acp/acp_search.php
@@ -9,16 +9,6 @@
*/
/**
-* @ignore
-*/
-if (!defined('IN_PHPBB'))
-{
- exit;
-}
-// make sure, a start time is saved
-still_on_time();
-
-/**
* @package acp
*/
class acp_search
@@ -27,7 +17,7 @@ class acp_search
var $state;
var $search;
var $max_post_id;
- var $batch_size = 1000;
+ var $batch_size = 100;
function main($id, $mode)
{
@@ -260,6 +250,7 @@ class acp_search
{
trigger_error($error . adm_back_link($this->u_action), E_USER_WARNING);
}
+ $name = ucfirst(strtolower(str_replace('_', ' ', $this->state[0])));
$action = &$this->state[1];
@@ -284,12 +275,15 @@ class acp_search
}
else
{
+ $starttime = explode(' ', microtime());
+ $starttime = $starttime[1] + $starttime[0];
+ $row_count = 0;
while (still_on_time() && $post_counter <= $this->max_post_id)
{
$sql = 'SELECT post_id, poster_id, forum_id
FROM ' . POSTS_TABLE . '
WHERE post_id >= ' . (int) ($post_counter + 1) . '
- AND post_id < ' . (int) ($post_counter + $this->batch_size);
+ AND post_id <= ' . (int) ($post_counter + $this->batch_size);
$result = $db->sql_query($sql);
$ids = $posters = $forum_ids = array();
@@ -300,6 +294,7 @@ class acp_search
$forum_ids[] = $row['forum_id'];
}
$db->sql_freeresult($result);
+ $row_count += sizeof($ids);
if (sizeof($ids))
{
@@ -307,15 +302,17 @@ class acp_search
}
$post_counter += $this->batch_size;
-
- // save the current state
- $this->save_state();
}
+ // save the current state
+ $this->save_state();
if ($post_counter <= $this->max_post_id)
{
+ $mtime = explode(' ', microtime());
+ $totaltime = $mtime[0] + $mtime[1] - $starttime;
+ $rows_per_second = $row_count / $totaltime;
meta_refresh(1, $this->u_action . '&amp;action=delete&amp;skip_rows=' . $post_counter);
- trigger_error(sprintf($user->lang['SEARCH_INDEX_DELETE_REDIRECT'], $post_counter));
+ trigger_error(sprintf($user->lang['SEARCH_INDEX_DELETE_REDIRECT'], $post_counter, $row_count, $rows_per_second));
}
}
@@ -324,6 +321,7 @@ class acp_search
$this->state = array('');
$this->save_state();
+ add_log('admin', 'LOG_SEARCH_INDEX_REMOVED', $name);
trigger_error($user->lang['SEARCH_INDEX_REMOVED'] . adm_back_link($this->u_action) . $this->close_popup_js());
break;
@@ -350,12 +348,15 @@ class acp_search
}
$db->sql_freeresult($result);
+ $starttime = explode(' ', microtime());
+ $starttime = $starttime[1] + $starttime[0];
+ $row_count = 0;
while (still_on_time() && $post_counter <= $this->max_post_id)
{
$sql = 'SELECT post_id, post_subject, post_text, poster_id, forum_id
FROM ' . POSTS_TABLE . '
WHERE post_id >= ' . (int) ($post_counter + 1) . '
- AND post_id < ' . (int) ($post_counter + $this->batch_size);
+ AND post_id <= ' . (int) ($post_counter + $this->batch_size);
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
@@ -366,19 +367,29 @@ class acp_search
{
$this->search->index('post', $row['post_id'], $row['post_text'], $row['post_subject'], $row['poster_id'], $row['forum_id']);
}
+ $row_count++;
}
$db->sql_freeresult($result);
$post_counter += $this->batch_size;
-
- // save the current state
- $this->save_state();
}
+ // save the current state
+ $this->save_state();
+
+ // pretend the number of posts was as big as the number of ids we indexed so far
+ // just an estimation as it includes deleted posts
+ $num_posts = $config['num_posts'];
+ $config['num_posts'] = min($config['num_posts'], $post_counter);
+ $this->search->tidy();
+ $config['num_posts'] = $num_posts;
if ($post_counter <= $this->max_post_id)
{
+ $mtime = explode(' ', microtime());
+ $totaltime = $mtime[0] + $mtime[1] - $starttime;
+ $rows_per_second = $row_count / $totaltime;
meta_refresh(1, $this->u_action . '&amp;action=create&amp;skip_rows=' . $post_counter);
- trigger_error(sprintf($user->lang['SEARCH_INDEX_CREATE_REDIRECT'], $post_counter));
+ trigger_error(sprintf($user->lang['SEARCH_INDEX_CREATE_REDIRECT'], $post_counter, $row_count, $rows_per_second));
}
}
@@ -387,6 +398,7 @@ class acp_search
$this->state = array('');
$this->save_state();
+ add_log('admin', 'LOG_SEARCH_INDEX_CREATED', $name);
trigger_error($user->lang['SEARCH_INDEX_CREATED'] . adm_back_link($this->u_action) . $this->close_popup_js());
break;
}
diff --git a/phpBB/includes/functions.php b/phpBB/includes/functions.php
index 04a2e20dce..2e8c87c04e 100644
--- a/phpBB/includes/functions.php
+++ b/phpBB/includes/functions.php
@@ -180,7 +180,7 @@ function unique_id($extra = 'c')
* @return bool Either true if the maximum execution time is nearly reached, or false
* if some time is still left.
*/
-function still_on_time()
+function still_on_time($extra_time = 15)
{
static $max_execution_time, $start_time;
@@ -194,10 +194,10 @@ function still_on_time()
// If zero, then set to something higher to not let the user catch the ten seconds barrier.
if ($max_execution_time === 0)
{
- $max_execution_time = 65;
+ $max_execution_time = 50 + $extra_time;
}
- $max_execution_time = min(max(10, ($max_execution_time - 15)), 50);
+ $max_execution_time = min(max(10, ($max_execution_time - $extra_time)), 50);
// For debugging purposes
// $max_execution_time = 10;
diff --git a/phpBB/includes/search/fulltext_native.php b/phpBB/includes/search/fulltext_native.php
index 5ff0c3c566..2d08a3020b 100755
--- a/phpBB/includes/search/fulltext_native.php
+++ b/phpBB/includes/search/fulltext_native.php
@@ -1110,7 +1110,6 @@ class fulltext_native extends search_backend
$word_ids[$row['word_text']] = $row['word_id'];
}
$db->sql_freeresult($result);
-
$new_words = array_diff($unique_add_words, array_keys($word_ids));
if (sizeof($new_words))
@@ -1121,8 +1120,9 @@ class fulltext_native extends search_backend
{
$sql_ary[] = array('word_text' => $word);
}
-
+ $db->return_on_error = true;
$db->sql_multi_insert(SEARCH_WORDLIST_TABLE, $sql_ary);
+ $db->return_on_error = false;
}
unset($new_words, $sql_ary);
}
@@ -1149,6 +1149,7 @@ class fulltext_native extends search_backend
}
}
+ $db->return_on_error = true;
foreach ($words['add'] as $word_in => $word_ary)
{
$title_match = ($word_in == 'title') ? 1 : 0;
@@ -1162,6 +1163,7 @@ class fulltext_native extends search_backend
$db->sql_query($sql);
}
}
+ $db->return_on_error = false;
// destroy cached search results containing any of the words removed or added
$this->destroy_cache(array_unique(array_merge($words['add']['post'], $words['add']['title'], $words['del']['post'], $words['del']['title'])), array($poster_id));
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php
index 1427ab69fe..b6b8e1f352 100644
--- a/phpBB/install/database_update.php
+++ b/phpBB/install/database_update.php
@@ -369,7 +369,10 @@ $database_update_info = array(
),
// Add the following unique indexes
'add_unique_index' => array(
- USERS_TABLE => array(
+ SEARCH_WORDMATCH_TABLE => array(
+ 'unique_match' => array('word_id', 'post_id', 'title_match'),
+ ),
+ USERS_TABLE => array(
'username_clean' => array('username_clean'),
),
),
diff --git a/phpBB/install/schemas/firebird_schema.sql b/phpBB/install/schemas/firebird_schema.sql
index 6fb0318cf2..00df42ac39 100644
--- a/phpBB/install/schemas/firebird_schema.sql
+++ b/phpBB/install/schemas/firebird_schema.sql
@@ -949,6 +949,7 @@ CREATE TABLE phpbb_search_wordmatch (
title_match INTEGER DEFAULT 0 NOT NULL
);;
+CREATE UNIQUE INDEX phpbb_search_wordmatch_unique_match ON phpbb_search_wordmatch(word_id, post_id, title_match);;
CREATE INDEX phpbb_search_wordmatch_word_id ON phpbb_search_wordmatch(word_id);;
CREATE INDEX phpbb_search_wordmatch_post_id ON phpbb_search_wordmatch(post_id);;
diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index e6da562358..8ef4c97c0d 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -1123,6 +1123,9 @@ CREATE TABLE [phpbb_search_wordmatch] (
) ON [PRIMARY]
GO
+CREATE UNIQUE INDEX [unique_match] ON [phpbb_search_wordmatch]([word_id], [post_id], [title_match]) ON [PRIMARY]
+GO
+
CREATE INDEX [word_id] ON [phpbb_search_wordmatch]([word_id]) ON [PRIMARY]
GO
diff --git a/phpBB/install/schemas/mysql_40_schema.sql b/phpBB/install/schemas/mysql_40_schema.sql
index 37eaa095ce..1402ef4303 100644
--- a/phpBB/install/schemas/mysql_40_schema.sql
+++ b/phpBB/install/schemas/mysql_40_schema.sql
@@ -648,6 +648,7 @@ CREATE TABLE phpbb_search_wordmatch (
post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
word_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
title_match tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
+ UNIQUE unique_match (word_id, post_id, title_match),
KEY word_id (word_id),
KEY post_id (post_id)
);
diff --git a/phpBB/install/schemas/mysql_41_schema.sql b/phpBB/install/schemas/mysql_41_schema.sql
index 0b4a2ce1d5..5362dbfdc5 100644
--- a/phpBB/install/schemas/mysql_41_schema.sql
+++ b/phpBB/install/schemas/mysql_41_schema.sql
@@ -648,6 +648,7 @@ CREATE TABLE phpbb_search_wordmatch (
post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
word_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
title_match tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
+ UNIQUE unique_match (word_id, post_id, title_match),
KEY word_id (word_id),
KEY post_id (post_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;
diff --git a/phpBB/install/schemas/oracle_schema.sql b/phpBB/install/schemas/oracle_schema.sql
index 4e5cbb3da0..b6d8bd7ac1 100644
--- a/phpBB/install/schemas/oracle_schema.sql
+++ b/phpBB/install/schemas/oracle_schema.sql
@@ -1260,7 +1260,8 @@ END;
CREATE TABLE phpbb_search_wordmatch (
post_id number(8) DEFAULT '0' NOT NULL,
word_id number(8) DEFAULT '0' NOT NULL,
- title_match number(1) DEFAULT '0' NOT NULL
+ title_match number(1) DEFAULT '0' NOT NULL,
+ CONSTRAINT u_phpbb_unique_match UNIQUE (word_id, post_id, title_match)
)
/
diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql
index f33fd7ec07..663f26cbed 100644
--- a/phpBB/install/schemas/postgres_schema.sql
+++ b/phpBB/install/schemas/postgres_schema.sql
@@ -862,6 +862,7 @@ CREATE TABLE phpbb_search_wordmatch (
title_match INT2 DEFAULT '0' NOT NULL CHECK (title_match >= 0)
);
+CREATE UNIQUE INDEX phpbb_search_wordmatch_unique_match ON phpbb_search_wordmatch (word_id, post_id, title_match);
CREATE INDEX phpbb_search_wordmatch_word_id ON phpbb_search_wordmatch (word_id);
CREATE INDEX phpbb_search_wordmatch_post_id ON phpbb_search_wordmatch (post_id);
diff --git a/phpBB/install/schemas/sqlite_schema.sql b/phpBB/install/schemas/sqlite_schema.sql
index 2a1875945f..a8620234b3 100644
--- a/phpBB/install/schemas/sqlite_schema.sql
+++ b/phpBB/install/schemas/sqlite_schema.sql
@@ -628,6 +628,7 @@ CREATE TABLE phpbb_search_wordmatch (
title_match INTEGER UNSIGNED NOT NULL DEFAULT '0'
);
+CREATE UNIQUE INDEX phpbb_search_wordmatch_unique_match ON phpbb_search_wordmatch (word_id, post_id, title_match);
CREATE INDEX phpbb_search_wordmatch_word_id ON phpbb_search_wordmatch (word_id);
CREATE INDEX phpbb_search_wordmatch_post_id ON phpbb_search_wordmatch (post_id);
diff --git a/phpBB/language/en/acp/common.php b/phpBB/language/en/acp/common.php
index 1f8b473ba4..d1b5af4df3 100644
--- a/phpBB/language/en/acp/common.php
+++ b/phpBB/language/en/acp/common.php
@@ -587,6 +587,8 @@ $lang = array_merge($lang, array(
'LOG_RESYNC_POST_MARKING' => '<strong>Dotted topics resynchronised</strong>',
'LOG_RESYNC_STATS' => '<strong>Post, topic and user statistics resynchronised</strong>',
+ 'LOG_SEARCH_INDEX_CREATED' => '<strong>Created search index for</strong><br />» %s',
+ 'LOG_SEARCH_INDEX_REMOVED' => '<strong>Removed search index for</strong><br />» %s',
'LOG_STYLE_ADD' => '<strong>Added new style</strong><br />» %s',
'LOG_STYLE_DELETE' => '<strong>Deleted style</strong><br />» %s',
'LOG_STYLE_EDIT_DETAILS' => '<strong>Edited style</strong><br />» %s',
generated by cgit v1.2.1 (git 2.21.0) at 2025-04-01 02:23:56 +0000