Sphinx 5-Primary index incremental index and live index

Original file Address: http://blog.itpub.net/29806344/viewspace-1400942/

When the database data is very large, and there is new data inserted in real time, if we do not update the index, the new data will not be search, all re-index and consumption of resources, in this case we need to use " primary index + incremental Index " to realize the real-time update function.

Because at this point we have the primary index and the incremental index, the primary index only needs to be updated every morning, and the update frequency of the incremental index is set very short, so that when the user searches, it can be found in both indexes.

First create a counter:

1. First insert a count table in MySQL

CREATE TABLE Sph_counter (

counter_id INTEGER PRIMARY KEY not NULL,

max_doc_id INTEGER not NULL

);

2. Modify the configuration file again, in the main data source we want to change the pre-query statement:

Vi/usr/local/coreseek/etc/csft.conf

SOURCE main{

Sql_query_pre = SET NAMES UTF8

Sql_query_pre = SET SESSION Query_cache_type=off

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)

...// other can default

}

SOURCE Delta:main// inherit data source

{

Sql_query_pre = SET NAMES UTF8

Sql_query_pre = SET SESSION Query_cache_type=off

Sql_query_pre =

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 Mian// primary index

{

Source = Main

Path=/usr/local/coreseek/var/data/main

}

Index Delta:main// Incremental Indexes

{

Source = Delta

Path=/usr/local/coreseek/var/data/delta

}

3. Rebuilding the incremental index

/usr/local/coreseek/bin/indexer-c/usr/local/coreseek/etc/csft.conf Delta

Retrieved using the/usr/local/coreseek/bin/search tool, the query results in the primary index are 0, and the new database is retrieved in the incremental index.

4. Updating indexes in real time

Build 2 shell scripts, one primary index script, one incremental index script

Vi main.sh

#!/bin/sh

/usr/local/coreseek/bin/indexer main–c/usr/local/coreseek/etc/csft.conf >>/usr/local/coreseek/var/log/ Main.log

Vi delta.sh

#!/bin/sh

/usr/local/coreseek/bin/indexer delta–c/usr/local/coreseek/etc/csft.conf–rotate>>/usr/local/coreseek/var/ Log/delta.log

Add to the scheduled task: re-index every 5 minutes; rebuild the main index every morning at 2 .

*/5 * * * */usr/local/coreseek/etc/delta.sh >/dev/null 2>&1

0 2 * * */usr/local/coreseek/etc/main.sh >/dev/null 2>&1

Sphinx 5-Primary index incremental index and live index