On Thu, Aug 7, 2014 at 5:51 AM, Paul Dillon <paul.dil...@gmail.com> wrote:
> 1. Will moving these 8 query fields to a smaller table improve query > performance when joined to the larger table? My logic is that this small > table would only be about 5% the size of the full table, so the full table > scan to service the query might be faster. > An alternative that could allow you to cheaply evaluate whether having the smaller 8-column table would be to index those 8 columns. See 1.7 Covering Indices from http://www.sqlite.org/queryplanner.html. Yes, you'd duplicate the data, and your DB would grow by 5% from your own admission, but then SQLite would be able to "route" queries selecting from that 8-column subset to the index and not read the table at all, and read the full table for other queries, transparently for you (analyze, to get good stats, which will take a long time, and then verify using explain query plan http://www.sqlite.org/eqp.html whether queries access the index, the table, or both). If you adjust your cache size to match or exceed the index size, you might be able to avoid disk IO altogether later, if allof your queries stayed within that subset, although it sounded like you load everything upfront which implies you don't query much later. The advantage of the above it that it requires very little effort from you, just a create index basically, to test that idea and see if it helps. --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
