Search code examples
sphinxthinking-sphinx

Sphinx Ranged query + killlist

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)
 }
Solution

  • 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.