[Toolkit] Optimizing phpBB meta keywords

SeO · Last edited by SeO on Mon Apr 02, 2007 2:01 pm; edited 4 times in total

Optimizing phpBB meta keywords :

We are going to see how to optimize phpBB meta Keywords, made with the phpBB SEO Dynamic Meta Tags which you must install before you go further with this method.
This optimization will as well concern you phpBB Search Tables.

Warning :

to be used with care

enough skills

some

advise you do lock your forum

local testing

full backup

Overview :

Introduction

Solutions

The French version of this article is located here.

SeO · Last edited by SeO on Mon Apr 02, 2007 2:01 pm; edited 1 time in total

Introduction

By excluding useless words in the search_stopwords.txt listing you will :

Enhance (a lot) your meta keywords;
The phpBB Search function will work faster, since it won't check for useless entries any more;
Page load and posting will be faster on your forum.

must backup all your forum and data base

not do it

lock

SeO · Last edited by SeO on Mon Mar 19, 2007 10:16 pm; edited 1 time in total

Solutions

Giefca's fix :

Code:

#
#--[ OPEN ]
#
includes/functions_search.php

#
#--[ FIND ]
#
if ( $mode == 'post' )
{
$entry = str_replace('*', ' ', $entry);

// 'words' that consist of <3 or >20 characters are removed.
$entry = preg_replace('/[ ]([\S]{1,2}|[\S]{21,})[ ]/',' ', $entry);
}

#
#--[ REPLACE WITH ]
#
// 3 letters fix by Giefca
if ( $mode == 'post' )
{
$entry = str_replace('*', ' ', $entry);

// 'words' that consist of <3 or >20 characters are removed.
$split = explode(' ', $entry);
$taille_split = sizeof($split);
for ($i = 0; $i < $taille_split; $i++)
{
$split[$i] = trim($split[$i]);
if ((strlen($split[$i]) < 3) || (strlen($split[$i]) > 20))
{
$split[$i] = '';
}
}
$entry = implode(' ', $split);
}
// 3 letters fix by Giefca

Please note that this patch will not get rid of the short words already listed in the search tables, it will only make sure they won't be added nor updated after you installed it.

Now we need to find out which words we should exclude both from the phpBB search tables and the meta keywords.

Please start phpMyAdmin and perform this SQL query :

Code:

SELECT ls.word_id, ls.word_text, COUNT(wm.word_id) as entries FROM `phpbb_search_wordlist` as ls LEFT JOIN `phpbb_search_wordmatch` as wm ON ls.word_id=wm.word_id GROUP BY wm.word_id ORDER BY entries DESC LIMIT 0,100

It will show you the 100 words the most used in your forum.
You will see how much your search tables do includes useless words.
The idea here is to find out among those 100 words which one we should exclude.

Once you have build up your list, you can go further and see if the next 100 are not listing many words to exclude as well, with this SQL :

Code:

SELECT ls.word_id, ls.word_text, COUNT(wm.word_id) as entries FROM `phpbb_search_wordlist` as ls LEFT JOIN `phpbb_search_wordmatch` as wm ON ls.word_id=wm.word_id GROUP BY wm.word_id ORDER BY entries DESC LIMIT 100,200

And so on, if your search tables are really filled with useless words, changing this part of the query :

Code:

LIMIT FIRST_WORD,LAST_WORD

There is no need to list too many words at a time, nor to go up to the top 1000 Wink

Then you just have to add those words in your search_stopwords.txt, one per line, without spaces at the end of the line.

Note that there is one search_stopwords.txt file per language, you will have to edit all of them.
According to the number of languages used in your forum, you can add all the words in every language in a single search_stopwords.txt you'll copy in every language folder; or build up a search_stopwords.txt per language.
In this case you should still put all of your excluded words in the search_stopwords.txt of your default language prior to dispatch them again after you'd have worked on the search tables. This way, you won't have to do the same filtering for every language.

Open up your search_stopwords.txt (\language\lang_english) or create a new one if there is none.
With the Giefca's patch, do not include words (and numbers since all is "text" here) of one and two letters in the listing.

It's a good thing to really build up a good words list at first, you won't have to clean your search table more than required.

In the end, we need to clean the search tables. To do this two solutions : Rebuild Search, which will totally rebuild your search tables or the simple script proposed in this article at phpBB.com.
The interesting part in it is the 4th : Delete search_stopwords from your search tables.

Rebuild Search is a bit heavier to use and will certainly take more time to run, but it can be very useful in a phpBB forum's life, because search table errors do occur and recovering search tables from backups can be tricky, especially with big forums.
In addition, if you implemented the Giefca's patch, words of less than three letters will be directly filtered, as the method used is cloning the posting process.

The phpBB.com is lighter and easier to use. It will only delete the newly excluded words from the search tables.
With Giefca's patch, you will still need to add the short words in the search_stopwords.txt the first time you run the script, and take them away once done (as they won't show up any more).

Once your list is completed, upload the new search_stopwords.txt and wait for the right time to either run Rebuild Search or the phpBB.com script.
Try the top 100 SQL query again and enjoy the difference.

There might be some useless words left, because they were not in the previous top 100 (or 200 etc ...).
No big deal, update your search_stopwords.txt and re-update your Search tables.
As well you can check your meta keywords to see if some less popular words should be excluded.
After a few cycles, your meta keywords should start to become very interesting Wink

With big forums, as there should be a lot of work to do, try to minimize the number of cycles you have to go through, using a good search_stopwords.txt.
And wait until you have found many more words to exclude before you go for another one.
And again, choose the right time to work on your search tables.

Rebuild Search has many options, here are the one advised here :

Time limit : Server's timeout, usually 30s, use 25 to be sure.
Starting post_id : Should be 0 for the first use as well as when you updated the search_stopwords.txt and go for a new cycle.
Posts per cycle : 50, no need to put too much here.
Disable board : Do it, this will prevent user from posting during the process, which should not be a problem with this mod, but if it's a 100 ...

You should delete your search tables when rebuilding them completely, after you have backup-ed everything obviously.

Lunch it, after the first 50 post will have been processed, you will see the graphs and details. You just have to wait until it ends.
Screenshots can be found here.

AmirAbbas

this fix doesn't work for UTF-8 charset
because each utf-8 character is equal with two iso-8859-1 charset

dcz

Actually, have you checked what was the smallest word you has in your search tables ?

I am not sure either about how to deal with UTF-8 characters here, they'd need to be converted to iso-8859-1 for this code to work I think.

You can still list UTF-8 words to exclude in your search_stopwords.txt and proceed without the Giefca's fix for now.

You'll still enhance your meta keywords a lot and end up with a smaller db.

You just need a longer search_stopwords.txt that's all.

AmirAbbas

dcz

Yes, if phpBB search function is able to deal with utf-8, then, if you put all words you don't want to see including the one and two letters ones, then, they won't be search able nor in your meta keywords Wink

Peter77

I've read this a couple times already. I want to understand what im doing before I try it lol. I know meta keywords are not as 'important' as they once used to be. but IMHO, I think they can be very useful to have. I've always had meta keywords on my portal, but not my phpbb. and then about a month ago I decided to add meta keywords to my forum. I've been noticing a really great improvement in search engines results from people looking for other sites that are similiar to mine. in some cases my site is on the first page with certain keywords. and this is just the Meta Keywords doing thier job ( I suspect ) as im still waiting for my indexed SEO friendly pages to show up in search engine results... imagine when that starts kicking in! Smile

dcz

Well, meta keywords where once very useful to Search Engine Optimize on selected keywords.

Now, the keyword list won't change much to search results querying them, but the meta tags themselves by being there and personalised for every page will help a lot.

Many page with the same meta description will for sure not be indexed and ranked as well as if they ad all a different one, just like titles, this is for sure.

Now, this specific how-to will just optimize a bit further. It's really worth it because it is as well cleaning your phpbb Search tables from unnecessary entries.

I'll soon release a optimized titles mod for phpbb. No big deal waiting, bots love when pages are updated so ...

++

Peter77

Ok... everything seemed to go well. are we supposed to keep search_stopwords.txt in our language folder from now on?

dcz

Exactly

one per language folder, can be the same copied over with words from all used languages, if not too big (I do it here for now, the file is 3.5 ko, not too much).

If you implemented the Giefca fix, no need to keep the word under three letter long in it after cleaning the search tables (nor before using rebuild search).

Fell free to start a thread to share our stop word lists, could be the [url]phpBB forum[/url] Wink

I think in most case we should end up with pretty much the same thing, at least a common base.

Anyway, this will be elaborated with time Wink

++

dcz

For the UTF-8 I have found this :

mb_strlen :

dcz

Or, this wonderful function can help even more : utf8_encode & utf8_decode

We could try this code then :

AmirAbbas

OHH

thanks
i will test it Razz

AmirAbbas

i tested it
it doesn't work for persian UTF-8 (maybe works for chinese or russian )
first i installed this fix
after that i sent a post that contains this word