htaccess Elite

.htaccess tutorial


All times are UTC [ DST ]





Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Most searched for words in phpbb
PostPosted: 01 Nov 2006 06:24 
Offline

Joined: 01 Nov 2006 05:19
Posts: 26
The search function of phpBB uses 3 tables:

  1. search_wordlist (this contains a unique entry for each word that has been posted in the forum)
  2. search_wordmatch (this contains pairs of data of which posts include which words from the search_wordlist)
  3. 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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 8 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
Powered by phpBB