An Easy Way to Replace SMF's Default Search with Sphinx

I needed to replace the default search on PykhoForum with Sphinx, and also implement a proper search by user. To do this, I had to install Sphinx and the SphinxSE engine for MySQL.

Preparing the index

Let’s write a Sphinx source:

source pyha_forum
{
    type = mysql
    sql_host = localhost
    sql_user = pyha
    sql_pass = 
    sql_db = pyha
    sql_port = 3306
    sql_sock = /tmp/mysql.sock

    sql_query_pre = SET NAMES utf8
    sql_query_pre = SET CHARACTER SET utf8

    sql_query = \
        select ID_MSG, concat('user:',posterName) as posterName, subject, body \
        from smf_messages

    sql_query_info = select ID_MSG, posterName, subject, body from smf_messages where ID_MSG = $id
    sql_ranged_throttle = 0
}

Searching by user is implemented using “concat(‘user:’,posterName)”. Simply enter “user:adw0rd php” in the search box to find all my posts related to php on Pykha.

Let’s write a Sphinx index:

index pyha_forum
{
    source = pyha_forum
    path = /var/sphinxsearch/pyha_forum
    docinfo = extern
    mlock = 0
    morphology = stem_enru
    min_word_len = 2
    charset_type = utf-8
    charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F
    min_infix_len = 2
    enable_star = 1
}

Next, we create a table for Sphinx:

CREATE TABLE `sphinx_smf_messages` (
  `id` int(11) NOT NULL,
  `weight` int(11) NOT NULL,
  `query` varchar(3072) NOT NULL,
  `group_id` int(11) default NULL,
  KEY `query` (`query`(1024))
) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION='sphinx://localhost:3312/pyha_forum';

More about SphinxSE: /2009/mysql-sphinxse/

Changing the code in SMF

In /Source/Search.php (around line 1377), find:

$request = db_query(
   "SELECT " . (empty($search_params['topic']) ? 'lsr.ID_TOPIC' : $search_params['topic'] . ' AS ID_TOPIC') . ", lsr.ID_MSG, lsr.relevance, lsr.num_matches
    FROM ({$db_prefix}log_search_results AS lsr" . ($search_params['sort'] == 'numReplies' ? ", {$db_prefix}topics AS t" : '') . ")
    WHERE ID_SEARCH = " . $_SESSION['search_cache']['ID_SEARCH'] . ($search_params['sort'] == 'numReplies' ? "
        AND t.ID_TOPIC = lsr.ID_TOPIC" : '') . "
    ORDER BY $search_params[sort] $search_params[sort_dir]
    LIMIT " . (int) $_REQUEST['start'] . ", $modSettings[search_results_per_page]", __FILE__, __LINE__);

and replace it with

$request = db_query(
   "SELECT `sm`.`ID_TOPIC`, `ssm`.`id` AS `ID_MSG`, 1000 AS `relevance`, 0 AS `num_matches`
    FROM `sphinx_smf_messages` AS `ssm` 
    LEFT JOIN `smf_messages` AS `sm`
        ON `ssm`.`id` = `sm`.`ID_MSG`
    WHERE `ssm`.`query` = '".$context['search_params']['search'].";weights=1,2,3'
    LIMIT " . (int) $_REQUEST['start'] . ", $modSettings[search_results_per_page]", __FILE__, __LINE__);

That’s it—we’ve solved the problem without any unnecessary complications! Anyone familiar with SMF’s source code will understand just how easy this solution turned out to be ;)

UP[2010-Feb-11]: Updated version of the search:

$searchQuery = preg_replace('/[^[a-z][а-я]\s\d_-]+/iu', ' ', $context['search_params']['search']);
$searchQuery = $searchQuery ? $searchQuery : '*';

$request = db_query(
   "SELECT `sm`.`ID_TOPIC`, `ssm`.`id` AS `ID_MSG`, 1000 AS `relevance`, 0 AS `num_matches`
    FROM `sphinx_smf_messages` AS `ssm` 
    LEFT JOIN `smf_messages` AS `sm`
        ON `ssm`.`id` = `sm`.`ID_MSG`
    WHERE `ssm`.`query` = '".$searchQuery.";index=pyha_forum,pyha_forum_delta;weights=30,20,10,5;offset=".intVal($_REQUEST['start']).
                 ";limit={$modSettings['search_results_per_page']};maxmatches=1000;mode=extended;groupby=day:created;sort=extended:@weight desc, created desc'",
    __FILE__, __LINE__
);

If you’re wondering what “pyha_forum_delta” is, read Sphinx. What Are Delta Indexes For, and How Do You Set Them Up?