This MOD replaces phpbb built-in search mechanism with mysql fulltext search.
there are several reasons why this will be useful for people who can use it, ie. those whose back-end database is mysql.
1 - first and foremost, phrase search ("word1 word2 word3")
2- size. the search_wordlist and search_wordmatch tables take more than 50% of the data, and their indices take much more than 50% of the index space. with mysql fulltext, there is no artificial data, which means cutting more than 50% on backup file size, and the index itself is also more efficient in space utilization.
3 - performance. i don't want to state here my opinion on phpbb built-in search, because i will be banned forever, but it is not good. every new message post result in twice as many queries as there are words in the post (yuck!), and the search itself is also problematic.
note that by using this mod, you lose the ability to search in post text only: all searches are on post title+post text.
i was pretty sure that someone have done it in the past, but i was somewhat disappointed to find that there is only one mod that implemented this, and this mod was abandoned in 2003, so i decided to write one myself.
what i did was a minimalistic job, where the goal was to implement the functionality with as little change as possible to the existing code. the change is in 4 files:
- search.php where the search is actually implemented
- includes/functions_search.php where the "phpbb native" handling of the search tables is neutralized (here is where you gain performance for posting and post editing)
- templates/subSilver/search_body.tpl :some changes to the search UI form, specifically removal of 2 radio-button groups: search all/any (this is handled through the keywords), and search in body+title/body only (this functionality is lost if you use this mod)
- language/english/lang_main.php: changed the explanation string of how to enter the search string
here is the main part of mod, i.e. the change to search.php.
Snipette
1) in search.php
Code: Select all
replace (around line 292)
for($i = 0; $i < count($split_search); $i++)
with:
$max_retrieve_results = 20000;
$phrase = mysql_escape_string($search_keywords);
$phrase = str_replace('\\\"', '"', $phrase);
$sql = "select post_id from " . POSTS_TEXT_TABLE .
" where match (post_subject, post_text) against('$phrase' in boolean mode)
limit $max_retrieve_results";
if ( !($result = $db->sql_query($sql)))
{
message_die(GENERAL_ERROR, 'Could not obtain matched posts list', '', __LINE__, __FILE__, $sql);
}
$total_posts = 0;
while($temp_row = $db->sql_fetchrow($result))
{
$result_list[$temp_row['post_id']] = 1;
}
for(; false ;)
add_search_words()
remove_common()
remove_search_post()
this is where the performance gain in posting, modifying and deleting a post are.
finally, add the search index, and truncate the old tables by performing the following queries:
(the first one builds the fulltext index, and with the last two you regain typically more than 50%, sometimes as much as 70% of your db size).
Code: Select all
alter table phpbb_posts_text add fulltext (post_subject, post_text);
truncate phpbb_search_wordlist;
truncate phpbb_search_wordmatch;
TO DO
test and make some measurements, both of size and performance.
find and fix the bug.
make the required changes to the search form. ******DONE*****
and finally, package it all in a standard MOD format. ****** DONE*******
if anyone volunteer to do any (or all) of these steps, i will gladly give them this whole thing, including responsibility and credit.
otherwise, i might and might not choose to do it myself.
have a good day.
TODO That someone else needs to do
Create similar MOD for other databases, or tell me how to do it.
the difference should only be in the little snipette i posted here, and the sql command to build the fulltext index.
[EDIT]: added MOD file, as per paul999 request [/EDIT]
File in old mod style
[2008 06 07 EDIT: fixed broken attachment link ]
) I use the following code, just to be safe:
So truncate should work.
If you truncate a table there is no undo. If you "delete * from table" you can undo. This is done by storing the state of the table in a temporary rollback segment until a commit is issued. Most of my DB experience is with Oracle, so I tend to extend the techniques learned there onto MySQL... sometimes with "interesting" results. 
(At the time I tried the other MOD I had about 4000 posts in my database but a good search_stopwords.txt.) Can someone confirm that with arod-1´s MOD the database will really decrease?