The data for each record is stored in the same order as the fields are defined. When reading a record's information, SQLite starts at the first field, and stops at the last field needed to complete the query. The situation where this makes the most difference is where there's a reasonably sized blob field, those should always be the final fields in the table's schema. If you have an important field after the large blob, SQLite has to load through the whole blob to get to that field, including going through any needed overflow pages (which are a linked list). If your query doesn't need that extra information, then SQLite can stop and not bother reading all that extra data. Since SQLite only reads whole pages at a time from disk though, that's mostly only relevant when you've got a lot of fields or large ones which result in overflow pages being needed.
The not loading more fields than needed is in part why you see a lot of sub queries that have "select 1 from", usually along the lines of "...where exists(select 1 from otherTable where...)..." By using the constant of 1 you avoid artificially making SQLite grab any more fields than what it needs for the where clause. Of course, I have been known to be wrong on these things. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Friday, March 17, 2017 2:40 PM To: SQLite mailing list Subject: Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ... 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
