Delta Indexing by timestamp in Sphinx

Question

I am attempting to setup a main+delta index scheme using a timestamp field instead of a max_doc_id as in the example in the docs.

source main
{
    sql_query_pre   = \
    REPLACE INTO sph_counter (counter_id, last_update_time) VALUES (1, NOW())

sql_query       = \
    SELECT id, filename, absolute_path, last_update_time \
    FROM files \
    WHERE last_update_time <= ( SELECT last_update_time FROM sph_counter WHERE counter_id=1)
}

source delta
{
sql_query       = \
    SELECT id, filename, absolute_path, last_update_time \
    FROM files \
    WHERE last_update_time > ( SELECT last_update_time FROM sph_counter WHERE counter_id=1)

sql_query_pre   =
}

The indexing and merging works as intended, however the sql_query_pre for the main source never updates the last_update_time in the sph_counter table.

I am unsure where my problem might be.

I am running this by doing the following:

re-index the delta       (every 30 seconds)

And:

merge delta into main    (every 10 mins)
re-index the delta       (after merge)

Am I wrong in thinking the merge will run the sql_query_pre to update the sph_counter last_update_time?

Answer 1

Am I wrong in thinking the merge will run the sql_query_pre to update the sph_counter last_update_time?

Yes. During a merge Sphinx does NOT run any of the queries the 'source' for the indexes. Its probably it doesnt even read the information from the config file, apart from getting the location of the index files.

The simplest way, would be to have the delta index also update a (seperate) counter, with its sql_query_pre.

Then during right after the merge, you can copy the timestamp from the delta record to the main record.