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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
