Improved search with Porter Stemmer Mod (Page 2 of 2)

I don't think the number of rows the search index contains is a problem per se. Properly indexed, searching through a table with a huge amount of rows is relatively fast for the database. There's topic somewhere on foruminsider.com discussing search indexing. It was quite interesting.

Also, I use "show as posts" all the time. I agree, it can be confusing, but if you know what you're searching for, it sure beats wading through 10 pages of posts.

I think the problem is that PunBB need to "juggle" around with quite large arrays of ID's. I haven't had time to do any real profiling on this, but I think that's where the bottleneck is. If the database is properly indexed and configured and you have performance issues, it is almost always in the scripts. I very seldom see a website being limited by e.g. MySQL. Apache/PHP are the thiefs.

I would use something like XDebug instead. It provides much more accurate information.

Rickard, take a look here:

http://wordindex.sourceforge.net/

This search engine is incredibly fast - and as far as I can see, the only major difference between this system and yours, is that the wordlinks (links from word IDs to post IDs) is split into multiple tables. Despite your faith in MySQL's ability to search a single enormous table quickly enough, this does not seem to be the case.

The indexer is done in PERL, but as said, is not really that different from yours, as far as I can tell - the table structures are very similar as well, but you may want to examine them for key differences; for one, a double-key is used in the word-link tables, I think this may be one of the key points to it's high performance.

The example on the website indexes 22.000 posts with 3 million wordlinks split across 100 tables, e.g. 20-50 thousand wordlinks per table. A search typically takes less than one second. Now, I know you may have forums with 100 times that number of posts - but the thing is, according to the programmer of this search engine, it doesn't have to get any slower, you can just split across an even greater number of tables; he has successfully split 14 GB of posts over 1000 tables, and still been able to search them in less than one second even on a "modest server".

The technique used to determine which table a word is located in, is simple - just take the modulus of the word ID and the number of tables, e.g. word 12345:

12345 % 100 = table 45

The search engine itself is done in PHP, so it should be easy for you to compare it to yours.

Just try the demo on the website, and you'll see, it's incredibly fast

I don't know if splitting across multiple tables is the only key to it's speed, there may be other key factors in the PHP script...

Rickard: upgraded to PunBB v1.2.5 - I wanted to do some search testing ... in common.php I enabled the following:

define('PUN_DEBUG', 1);
define('PUN_SHOW_QUERIES', 1);

but it doesn't seem to work properly, it doesn't display all the queries? namely the interesting queries - word lookups and wordlink lookups - are not shown in the output??

I have been doing a closer examination of the data structures.

You have chosen to make `search_words`. `word` a VARCHAR(20), which doesn't really seem to make any sense ... a CHAR would give faster searches ... of course, this comes at the cost of more diskspace usage, but in my database with about 15.000 words, it only grew from 590KB to 648KB - because yes, the word records as such will take up more space, but the search index will also take up less space. So this seems like a sensible change - using VARCHAR instead of CHAR for strings as small as 20 chars, doesn't really make any sense.

This seems to be the only relevant difference between your table structures and the ones used in wordindex.

As you noted earlier, a major bottleneck at the moment seems to be the PHP script time itself, perhaps more so than the actual database queries, so the above change may give a barely noticeable performance increase for most searches...

So I'm looking at your search code, and there is a major difference between the way you do it, and the way it's done in wordindex.

PunBB walks through the wordlinks one at a time (the while-loop starting at line 167 in search.php).

Wordindex gets all of the wordlinks, sticks them in one array per word, and then uses one function call to combine those arrays.

Clearly, if you can get away with one function call instead of a whole bunch of function calls, this is going to give you a massive speed advantage in those cases where a word has lots of wordlinks, because looping through them in your script is a slow and heavy process compared to what a native, compiled function can achieve.

So what you might want to do, is try to avoid using loops - use single calls instead, and you should get a considerable performance increase.

Strangely enough, the MySQL library apparently does not provide a function to get all rows from a result to an array with one function call? You will still have to get get them one at a time and push them to an array.

Once you have all of your wordlink arrays (one array per word) filled, you can then join them with a single function call, which should give a notable speed increase.

Try array_intersect() for "OR" searches, and array_diff() for "AND" searches ... should considerably faster than doing similar operations manually?