title: Deleting rows from a sqlite FTS table is slow
body: <p>I am using <strong>sqlite 3.8.2</strong> and I'm trying to delete a lot of rows from <strong>my full text table</strong> <code>media_fts</code> with the following query:</p>
  
  <pre><code> DELETE FROM `media_fts`
    WHERE docid IN
          ( SELECT id
              FROM media
             WHERE is_in_music_folder = 1)
  </code></pre>
  
  <p>The query plan the query is the following: </p>
  
  <pre><code>SCAN TABLE media_fts VIRTUAL TABLE INDEX 1: 
  EXECUTE LIST SUBQUERY 0
  SEARCH TABLE media USING COVERING INDEX media_idx_extra_1 (is_in_music_folder=?)
  </code></pre>
  
  <p>As you can see the index <code>media_idx_extra_1</code> is properly used.</p>
  
  <p>The deletion concerns <strong>500 000 rows</strong> and takes <strong>34s</strong> which is very slow ; for a comparison, deleting the same amount of rows in the <code>media</code> database takes <strong>89ms</strong>.</p>
  
  <p>The FTS table is pretty straightforward: </p>
  
  <pre><code>    CREATE VIRTUAL TABLE IF NOT EXISTS media_fts
          USING fts4 (
              media_id,
              album_id,
              title,
              artist,
              album_artist,
              album,
              comment,
              lyrics,
              is_in_music_folder  INT NOT NULL DEFAULT 0
          ) ;
  </code></pre>
  
  <p>What is wrong with my query ?</p>
  
  <blockquote>
    <p>Note: my database has been VACUUMED and ANALYZED</p>
  </blockquote>
  
  <p><strong>Update</strong> 
  As per @ah_hau's suggestion I tried the following query: </p>
  
  <pre><code>DELETE FROM `media_fts`            
   WHERE docid IN        
       ( SELECT id            
       FROM media           
       INNER JOIN media_fts ON (media.id = media_fts.docid)            
       WHERE  media.is_in_music_folder = 1)
  </code></pre>
  
  <p>with the plan: </p>
  
  <pre><code>SCAN TABLE media_fts VIRTUAL TABLE INDEX 1: 
  EXECUTE LIST SUBQUERY 0 
  SEARCH TABLE media USING COVERING INDEX media_idx_extra_1 (is_in_music_folder=?) 
  SCAN TABLE media_fts VIRTUAL TABLE INDEX 1: 
  </code></pre>
  
  <p>The new query takes <strong>54s</strong> so not an improvement </p>