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
SELECT * FROM MyTable;
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