1. 12 Mar, 2014 1 commit
    • Charles Lindsay's avatar
      Fix a number of database hiccups · c3e52705
      Charles Lindsay authored
      1) Use docid instead of id in search table.
      
      We had previously included an 'id INTEGER PRIMARY KEY' column in the
      MessageSearchTable, assuming it would get the same rowid alias treatment
      as it does in non-FTS tables.  That assumption was wrong: it was being
      created as a FTS column.  This fixes it so we use docid everywhere.
      
      To fix the old incorrect docid values, we simply blow away the search
      table and let the natural search table population process, which now has
      the correct docid insertion code, fix the problem.
      
      This also removes the id column from the search table creation SQL, but
      this will only affect new users.  Upgraders will see an empty, vestigal
      id column in their search table.  Since SQLite doesn't easily let you
      remove columns, it's just easier to ignore the column than go through
      all the work to fix it.
      
      2) Do as many rowid lookups as possible in batches, instead of doing
      them individually in loops.  This speeds up working with large sets of
      email.
      
      3) Rejigger indices on the MessageLocationTable to make certain queries
      faster.  This creates a new covering index in particular for the email
      prefetcher, which previously had to sort using a temp table.  The new
      index should work in the general case too, as we should never be looking
      at ordering without folder_id (and since folder_id comes first, it works
      as an index on just folder_id, too).
      
      4) For bonus measure, log all slow queries (> 1s execution time) to
      debug output.
      
      Closes: bgo #725929
      c3e52705