title: Sphinx main/delta indexing, sql_query_killlist
body: <p>I am currently using Sphinx for indexing a MySQL query with 20+ million records.</p>
  
  <p>I am using a delta index to update the main index and add all new records. </p>
  
  <p>Unfortunately allot of changes to the tables are deleted. </p>
  
  <p>I understand that I can use sql_query_killlist to get all document ID's that need to be deleted or updated. Unfortunately I don't understand how this actually works and the documentation from Sphinx does not have a good enough example for me to understand.</p>
  
  <p>If I use the following example, how could I implement the killlist?</p>
  
  <p>in MySQL</p>
  
  <pre><code>CREATE TABLE sph_counter
  (
      counter_id INTEGER PRIMARY KEY NOT NULL,
      max_doc_id INTEGER NOT NULL
  );
  </code></pre>
  
  <p>in sphinx.conf</p>
  
  <pre><code>source main
  {
      # ...
      sql_query_pre = SET NAMES utf8
      sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(id) FROM documents
      sql_query = SELECT id, title, body FROM documents \
          WHERE id&lt;=( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
  }
  
  source delta : main
  {
      sql_query_pre = SET NAMES utf8
      sql_query = SELECT id, title, body FROM documents \
          WHERE id&gt;( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
  }
  </code></pre>
  
  <p>index main</p>
  
  <pre><code>{
      source = main
      path = /path/to/main
      # ... all the other settings
  }
  </code></pre>
  
  <p>note how all other settings are copied from main,
  but source and path are overridden (they MUST be)
  index delta : main</p>
  
  <pre><code>{
      source = delta
      path = /path/to/delta
  }
  </code></pre>