xoops forums

birdseed

Just popping in
Posted on: 2006/7/26 10:30
birdseed
birdseed (Show more)
Just popping in
Posts: 59
Since: 2005/2/26
#1

This will make the cbb Forum search > 100 times faster

Hi

A MySQL FULLTEXT index will make the search in a big forum (>300.000 postings) faster (and btw. fix a huge denial-of-service security hole which can be used to block a whole XOOPS site). MySQL >= 4.1 is required.

1. Go to modules/newbb/include/search.php

replace the switch($searchin) {....} through the following:

switch ($searchin) {
           case 
'title':
               
$sql .= " AND (MATCH(p.subject) AGAINST('$queryarray[0]' IN BOOLEAN MODE)";
               for(
$i=1;$i<$count;$i++){
                   
$sql .= $andor ";
                   
$sql .= "MATCH(p.subject) AGAINST('$queryarray[$i]' IN BOOLEAN MODE)";
               }
               
$sql .= ") ";
               break;

           case 
'text':
               
$sql .= " AND (MATCH(pt.post_text) AGAINST('$queryarray[0]' IN BOOLEAN MODE)";
               for(
$i=1;$i<$count;$i++){
                   
$sql .= $andor ";
                   
$sql .= "MATCH(pt.post_text) AGAINST('$queryarray[$i]' IN BOOLEAN MODE)";
               }
               
$sql .= ") ";
               break;
            case 
'both' :
            default;
               
$sql .= " AND ((MATCH(p.subject) AGAINST('$queryarray[0]' IN BOOLEAN MODE) OR MATCH(pt.post_text) AGAINST('$queryarray[0]' IN BOOLEAN MODE))";
               for(
$i=1;$i<$count;$i++){
                   
$sql .= $andor ";
                   
$sql .= "(MATCH(p.subject) AGAINST('$queryarray[$i]' IN BOOLEAN MODE) OR MATCH(pt.post_text) AGAINST('$queryarray[$i]' IN BOOLEAN MODE))";
               }
               
$sql .= ") ";
               break;
        }


2. Use phpmyadmin to add a fulltext index to the following columns:

YOURPREFIX_bb_posts.subject
YOURPREFIX_bb_posts_text.post_text

(go to the table structure and press the "T" Button in the column to add a Fulltext search index). This will take up to 5 minutes for big forum tables.

3. Lower the min length for a search word to 4 in the XOOPS preferences.

greetings
birdseedmusic

neill

Just popping in
Posted on: 2006/8/1 14:47
neill
neill (Show more)
Just popping in
Posts: 16
Since: 2006/7/23
#2

Re: This will make the cbb Forum search > 100 times faster

good idea, but i dont think it will work good in other languages, like chinese

phppp

XOOPS Contributor
Posted on: 2006/8/1 14:58
phppp
phppp (Show more)
XOOPS Contributor
Posts: 2857
Since: 2004/1/25
#3

Re: This will make the cbb Forum search > 100 times faster

There is a topic on sourceforge
http://sourceforge.net/forum/forum.ph ... d=1520500&forum_id=347994