On 15 Oct 2016, at 7:34pm, Jens Alfke <j...@mooseyard.com> wrote:

> I do, actually, which is why I asked. One of the columns is a blob holding a 
> JSON document that can be arbitrarily large. It sounds like including this 
> column in the SELECT clause will cause the entire blob to be read from disk? 
> I definitely don’t want that.

When reading a row of the data asked for SQLite reads through the first column 
of the row to the end of the column which is last in the table.  In other words

CREATE TABLE MyTable (a, b, c, d, e, f, g, h);
SELECT c, f FROM MyTable;

means that SQLite has to read through columns from a to f.  It won't fetch the 
data from long TEXT or BLOB values but it needs to know how long they are to 
figure out where the following column's value starts.

So if you don't need column f don't ask for it.  And definitely don't use


unless that's what you need.

The additional twist that Dominique mentioned is that in SQLite an index entry 
contains the values it's indexing.  So suppose you have the above table and

CREATE INDEX i1 on MyTable (c, d, e);
SELECT c, d, e, f FROM MyTable ORDER BY c, d;

then if your SELECT ends up using that index the only value SQLite actually 
needs to fetch from the table is the one for column f.  Because it already has 
the values for c, d and e because it just read them from the index.  And if 
SQLite doesn't need to read any values from the table it doesn't bother reading 
the table at all. Which speeds things up a lot.

sqlite-users mailing list

Reply via email to