title: Why don't we always use FTS tables?
body: <p>The wording of my question comes from a comment at the end of the blog post <a href="http://blog.andresteingress.com/2011/09/30/android-quick-tip-using-sqlite-fts-tables/" rel="nofollow noreferrer">Android Quick Tip: Using SQLite FTS Tables</a>. As the title implies, the post tells how to create and query full text search virual tables in your android app. The comment by user <a href="https://disqus.com/by/ferraviola/" rel="nofollow noreferrer">Fer Raviola</a> specifically reads</p>
  
  <blockquote>
    <p>my question is why dont' we ALWAYS use FTS tables!. I mean, they ARE
    faster</p>
  </blockquote>
  
  <p>The blog author did not reply (at the time of this writing anyway), but I thought it was an interesting question that deserves an answer. After all, FTS tables can be made for an entire table, not just a specific text column. At first look, it seems like it would both simply and speed up queries.</p>
  
  <p>One could also completely do away with the non-virtual table. That would eliminate having to keep the virtual and non-virtual tables in sync with <a href="http://www.sqlite.org/lang_createtrigger.html" rel="nofollow noreferrer">triggers</a> and <a href="http://www.sqlite.org/fts3.html#section_6_2_2" rel="nofollow noreferrer">external content tables</a>. All the data would be stored in the virtual table.</p>
  
  <p><a href="https://stackoverflow.com/a/14101166/3681880">@CL. says</a> that this is not a good option, though, because "FTS tables cannot be efficiently queried for non-FTS searches." I assume that this has something to do with what the <a href="http://www.sqlite.org/fts3.html#section_1_4" rel="nofollow noreferrer">SQLite documentation says here</a>:</p>
  
  <pre><code>-- The examples in this block assume the following FTS table:
  CREATE VIRTUAL TABLE mail USING fts3(subject, body);
  
  SELECT * FROM mail WHERE rowid = 15;                -- Fast. Rowid lookup.
  SELECT * FROM mail WHERE body MATCH 'sqlite';       -- Fast. Full-text query.
  SELECT * FROM mail WHERE mail MATCH 'search';       -- Fast. Full-text query.
  SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;   -- Slow. Linear scan.
  SELECT * FROM mail WHERE subject = 'database';      -- Slow. Linear scan.
  SELECT * FROM mail WHERE subject MATCH 'database';  -- Fast. Full-text query.
  </code></pre>
  
  <p>But are the slow queries really so much slower than if one were just doing a normal query on a normal table? If so, why?</p>
  
  <p>Here are some general potential downsides that I can think of to only using a virtual FTS table in Android:</p>
  
  <ul>
  <li>The table would be larger because of the size that the index takes.</li>
  <li>Operations like INSERT, UPDATE, and DELETE would be slower because the index would have to be updated.</li>
  </ul>
  
  <p>But as far as queries themselves go, I don't see what the problem would be. </p>
  
  <p><strong>Update</strong></p>
  
  <p>The Android documentation example <a href="http://developer.android.com/training/search/search.html" rel="nofollow noreferrer">Storing and Searching for Data</a> uses only an FTS virtual table in its database. This seems to confirm that there are at least some viable options for FTS only databases.</p>