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.

-----Original Message-----
From: sqlite-users [] 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 <> 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.

sqlite-users mailing list
sqlite-users mailing list

Reply via email to