sphinx incremental index implementation example

Note: Sphinx’s incremental index is actually implemented through two indexes (the main index is updated every morning, and the incremental index is generated every 5 minutes). It is said on the Internet that it can be merged into one index through index merge, but I tried it. Try not to actually merge it in

sphinx incremental index setting The existing data in the database is very large, and new data is constantly being added to the database, and I hope it can be retrieved. Full re-indexing is expensive because the data we need to update is relatively small. For example. There were millions of original data, but only a few thousand new ones. In this way, the "main index + incremental index" mode can be used to achieve near real-time update functionality.
This The basic principle of implementing this mode is to set up two data sources and two indexes, and establish a main index for those data that are basically not updated, and for those that are new Create incremental indexes for incremental data. The update frequency of the main index can be set longer (for example, set at midnight every day), while the update frequency of the incremental index can be set very short (a few minutes or so). Right), so that when the user searches, we can query the data of these two indexes at the same time.
There is a simple implementation using the "main index + incremental index" method. Add a counting table in the database to record the last data ID of the indexed table each time the main index is rebuilt, so that only the incremental index can be used. You only need to index the data after this ID, and update this table every time the main index is rebuilt.
Test conditions: Take the default sphinx.conf configuration as an example, and the database table data as example.sql.

1. Create related tables

创建主索引表
 CREATE TABLE `sph_test1` (
  `id` int(10) unsigned NOT NULL,
  `weight` int(11) NOT NULL,
  `query` varchar(3072) CHARACTER SET latin1 NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  KEY `query` (`query`)
) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION='sphinx://127.0.0.1:9312/test1'
创建索引计数表
     CREATE TABLE `sph_counter` (
  `id` int(11) NOT NULL,
  `max_doc_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)
创建增量索引表
 CREATE TABLE `sph_delta_test1` (
  `id` int(10) unsigned 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://127.0.0.1:9312/delta_test1'

2. Modify sphinx.conf

source src1{
       type                = mysql
       sql_host            = localhost
       sql_user            = yourusername
       sql_pass            = yourpassword
       sql_db              = test   //你所用的数据库
       sql_port            = 3306 //所用端口，默认是3306
       sql_query_pre       = SET NAMES utf8
       sql_query_pre       = sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(id) FROM documents 
       sql_query = SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title,\
                 content FROM documents \
               WHERE id<=( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
}
// 注意：delta_src 中的sql_query_pre的个数需和main_src 对应，否则可能搜索不出相应结果
source delta_src1: src1{
         sql_ranged_throttle = 100
         sql_query_pre       = SET NAMES utf8
         sql_query_pre       = SET SESSION query_cache_type=OFF
         sql_query      = SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content FROM documents\
            WHERE id>( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
}
index test1 //主索引{
       source           = src1
       path             = /usr/local/sphinx/var/data/test1
       charset_type     = utf-8    #这个是支持中文必须要设置的
       chinese_dictionary =/usr/local/sphinx/etc/xdict       #..........其它可以默认
}
 
index delta_test1: src1 //增量索引{
         source = delta_src1
         path     = /usr/local/sphinx/var/data/delta_src1
}

3. Generate index by scheduled task (--rotate parameter can take effect without restarting the service index)

a. Generate full index every early morning: ./indexer test1 --rotate

b. Generate incremental index every 10 minutes: ./indexer delta_test1 --rotate