Sphinx Ranged query + killlist

Question

i have a sphinx instance with two indexes configured: main and delta. Both of them have sql query range.

in the delta index i have a killlist query to remove modified articles from the main index.

should this query be ranged like the content query ?

i.e.

source delta : main {
  sql_query_range   = SELECT MIN(id),MAX(id) FROM documents
  sql_range_step = 1000

  sql_query = SELECT * FROM documents WHERE id>=$start AND id<=$end WHERE ID > (SELECT maxID from SphinxTable)

  sql_query_killlist = SELECT id FROM documents WHERE id>=$start AND id<=$end WHERE ID > (SELECT maxID from SphinxTable)
 }

Your `sql_query_killlist` is the same as your `sql_query`. Is it intended? — Tchoupi, Sep 13 '12 at 11:31
yes, documents indexed by the delta index should be removed from preceding indexes (main) — madoke, Sep 13 '12 at 11:35

score 2 · Accepted Answer · answered Sep 13 '12 at 11:33

should this query be ranged like the content query ?

No. killlists dont support ranged queries. It just runs one query.

Incidently, this:

sql_query_range = SELECT MIN(id),MAX(id) FROM documents

looks wrong. That is taking ALL ids from the documents table. But the sql_query has an aditional caluse using maxID from SphinxTable

Should be be something like

sql_query_range   = SELECT (SELECT maxID from SphinxTable),MAX(id) FROM documents

Otherwise your are going to be issuing lots of queries - to fetch documents that would be in main, which will never match because of the second calise.

So just do

sql_query = SELECT * FROM documents WHERE id>=$start AND id<=$end WHERE ID > (SELECT  maxID from SphinxTable) OR updated > (SELECT updatedts FROM SphinxTable)
sql_query_killlist = SELECT id FROM documents WHERE id <= (SELECT maxID from SphinxTable) AND updated > (SELECT updatedts FROM SphinxTable)

Note the change in equality. You want documents that are in the main, in your killlist. But you also only want the documents updated since the last reindex.

Sphinx Ranged query + killlist

1 Answers1