The search function of phpBB uses 3 tables:
- search_wordlist (this contains a unique entry for each word that has been posted in the forum)
- search_wordmatch (this contains pairs of data of which posts include which words from the search_wordlist)
- search_results (this contains the last searches the users executed)
phpBB marks heavily used words by changing the "word_common" field in the search_wordlist from 0 to 1 and then
deleting them from the search_wordmatch table.
Heavily used words are those who appear in the search_wordmatch table more than (4/10 * total_posts) times.
phpBB doesn't check for common words before adding them into the tables. It justs adds them first
and then deletes them if they are found to be common.
Also phpBB uses a blacklist of words (a text file containing one word per line) that you don't want to be indexed.
If you are using the default english language, this file is "language/lang_english/search_stopwords.txt".
If you want to find your most commonly used words, so you can put some of them into this blacklist,
you can run the following db query in phpmyadmin
Code:
SELECT swl.word_id, swl.word_text, COUNT(swm.word_id) as total
FROM `phpbb_search_wordlist` swl, `phpbb_search_wordmatch` swm
WHERE swl.word_id = swm.word_id
GROUP BY swm.word_id
ORDER BY total
DESC LIMIT 0,50
Also, you may want to check for inconsistencies in the phpbb post tables using the following 2 sql queries:
Code:
SELECT p.*
FROM phpbb_posts p
LEFT JOIN phpbb_posts_text pt ON p.post_id = pt.post_id
WHERE pt.post_id IS NULL;
Code:
SELECT pt.*
FROM phpbb_posts_text pt
LEFT JOIN phpbb_posts p ON p.post_id = pt.post_id
WHERE p.post_id IS NULL;
If you get any output from the above 2 queries, it means you have some records in one table but not their
pair record on the other table. So it should be better to fix them before running this mod.
So, if you can't find a keyword, which you're sure it exists in your posts, then please make sure that:
- It's not included in your "language/lang_xxx/search_stopwords.txt" file. (The words included in this file, are not indexed in the search tables.)
- It hasn't been flagged as too common in the db. You can run the following query in order to check that:
Find words marked as "too common" by phpbb:Code:
SELECT *
FROM `phpbb_search_wordlist`
WHERE `word_common` !=0
LIMIT 0 , 3000
Register
Login
