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
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.
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
Sleep while waiting for indexes to be created and ANALYZE to work. May you see
skilled players, creative moves and dramatic play.
sqlite-users mailing list
sqlite-users mailing list