Sphinx Search

[ivanp]

In vB 3.7.0 & vB Blog 1.0.4 the following tables have FULLTEXT indices:

post -> FULLTEXT KEY title (title, pagetext);
thread -> FULLTEXT KEY title (title);
blog -> FULLTEXT KEY title (title);
blog_text -> FULLTEXT KEY title (title,pagetext);
socialgroup -> FULLTEXT KEY name (name, description);

The idea is to find a way to use Sphinx to completely replace vB FULLTEXT queries and to drop all these indices.

And then to switch to InnoDB tables, to prevent the rest of the locking queries.

--------------- Added [DATE]1210415448[/DATE] at [TIME]1210415448[/TIME] ---------------

Here is my setup... We should bundle similar script in the package.


Cron jobs:

• Every week: /usr/sphinx/indexer.sh --all
• Every hour: /usr/sphinx/indexer.sh thread
• Every 5 minutes: /usr/sphinx/indexer.sh postdelta threaddelta

indexer.sh:

#!/bin/sh

LOCKFILE=/var/lock/sphinx.cron.lock
INDEXER_CONF=/usr/sphinx/etc/sphinx.conf
INDEXER_BIN=/usr/sphinx/bin
INDEXER_LOG=/var/log/sphinx/indexer.log

[ -f $LOCKFILE ] && exit 0

trap "{ rm -f $LOCKFILE ; exit 255; }" EXIT

touch $LOCKFILE

$INDEXER_BIN/indexer --config $INDEXER_CONF --rotate $1 $2 $3 $4 >> $INDEXER_LOG

exit 0