On 17 Mar 2017, at 6:22pm, Rob Willett <rob.sql...@robertwillett.com> wrote:
> 4. Work through returning just the columns we actually need from our queries. > We have a recollection that if we can build an index with all the information > necessary in it, we can do all the work in joins rather than paging out to > disk. Is this what you are referring to? It works only where all the columns you need to read are in the same table. The ideal form of a covering index is to have the columns listed in this order: 1) columns needed for the WHERE clause 2) columns needed for the ORDER BY clause which aren’t in (1) 3) columns needed to be read which aren’t in (2) or (1) SQLite detects that all the information it needs for the SELECT is available from the index, so it doesn’t bother to read the table at all. This can lead to something like a doubling of speed. Of course, you sacrifice filespace, and making changes to the table takes a little longer. > 5. Sleep (not exactly sure when) and watch three international rugby games > tomorrow. Sleep while waiting for indexes to be created and ANALYZE to work. May you see skilled players, creative moves and dramatic play. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
