Sphinx. What Are Delta Indexes For, and How Do You Set Them Up?

Delta indexes let you use a small index instead of reindexing the entire database at short intervals.

For example, suppose we have a very large number of records in the database—hundreds of thousands or several million—and new data arrives very frequently. We need to index it, but running indexing every 5–10 minutes is too resource-intensive.

So we create two indexes:

• Full — indexes all the data in its entirety (run once a day)
• Delta — indexes only the small portion of data added during the day (run every 5–10 minutes)

But how do we determine where to start indexing data for the delta index?

We need to remember the ID of the last record indexed in the full index and start indexing data from that point. It’s very simple :)

To do this, let’s create another table in the database to store the ID of the last record in the full index:

CREATE TABLE `sphinx_delta_counter` (
    `index_name` ENUM( 'pyha_forum' ) NOT NULL,
    `last_post_id` INT UNSIGNED NOT NULL
) ENGINE = InnoDB;

Where,

• “index_name” — the name of the index, for example if you have many indexes for different tables (you can omit this if you have only one index);
• “last_post_id” — the last record ID for a particular index.

Let’s insert a record for our index so we can update the counter later:

INSERT INTO `sphinx_delta_counter`
    SET `index_name` = 'pyha_forum', `last_post_id` = 0;

Of course, you could also use this query in the Sphinx index, first rewriting it to use “on duplicate key” and adding a unique index on the combination “index_name+last_post_id”, but I think that’s unnecessary.

Building Indexes in Sphinx

Here’s a sample configuration file, based on the example from the previous article, /2009/smf-sphinx/.

# сорс для подключения к БД, далее его будем наследовать в других сорсах
source dbconnect
{
    type = mysql
    sql_host = localhost
    sql_user = pyha
    sql_pass = 
    sql_db = pyha
    sql_port = 3306
    sql_sock = /tmp/mysql.sock

}

# сорс полного-индекса
source pyha_forum : dbconnect
{

    sql_query_pre = SET NAMES utf8
    sql_query_pre = SET CHARACTER SET utf8

    # обновляем наш счетчик, добавляем туда крайний ид записи для дельта-индекса
    sql_query_pre = \
        update sphinx_delta_counter \
        set last_post_id = (select max(ID_MSG) from smf_messages) \
        where index_name = 'pyha_forum';

    # выбираем все данные для полного-индекса
    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
}

# сорс дельта-индекса
source pyha_forum_delta : dbconnect
{

    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 \
        where ID_MSG > (select last_post_id from sphinx_delta_counter where index_name = 'pyha_forum');

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

# полный-индекс
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
}

# дельта-индекс
index pyha_forum_delta : pyha_forum
{
    source = pyha_forum_delta
    path = /var/sphinxsearch/pyha_forum_delta
}

# настройки индексатора
indexer
{
    mem_limit = 32M
}

# настройки поискового демона
searchd
{
    address = 127.0.0.1
    port = 3312
    log = /var/sphinxsearch/log/searchd.log
    query_log = /var/sphinxsearch/log/query.log
    read_timeout = 5
    max_children = 30
    #pid_file = /var/sphinxsearch/log/searchd.pid
    max_matches = 1000
}

That’s it, Sphinx is configured. Next, let’s schedule reindexing in cron.

Scheduling in Cron

0 1 * * * /usr/local/bin/indexer --config /usr/local/etc/sphinx.conf.sh pyha_forum --rotate > /dev/null
*/5 * * * * /usr/local/bin/indexer --config /usr/local/etc/sphinx.conf.sh pyha_forum_delta --rotate > /dev/null

Where,

1. Index the full index every day at 1 a.m.
2. Index the delta index every five minutes

Not sure what the configuration file “/usr/local/etc/sphinx.conf.sh” is? Then read the article Including Sphinx Config Files.

Adding Delta Index Searching to the Code

Since I use SphinxSE, here’s how it needs to be added:

`query` = "поисковый запрос;index=pyha_forum,pyha_forum_delta"

Searches will then run against both the full and delta indexes!

Integrating into SMF

Next, since we’re working through an example based on the article /2009/smf-sphinx/, let’s once again find this code in “/Source/Search.php” (around line 1377):

$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__);

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;index=pyha_forum,pyha_forum_delta'
    LIMIT " . (int) $_REQUEST['start'] . ", $modSettings[search_results_per_page]", __FILE__, __LINE__);

That’s all :)