On 10/29/15, Wade, William <bill.wade at dnvgl.com> wrote: > From https://www.sqlite.org/fileformat2.html, if a row does not fit into a > single block, its overflow is stored in a linked list. The implication (to > me) is that if a particular row requires twenty blocks of storage, and I > want to read a one-byte field that is in the twentieth block, all twenty > blocks will be read.
(1) We encourage developers to put small boolean and integer value fields, and frequently accessed fields, first in the CREATE TABLE statement, and put huge strings and blobs at the end, for exactly this reason. (2) SQLite is able to skip intermediate blocks on the linked list sometimes, when the database is in incremental- or auto-vacuum mode. It's now 100%* but it works pretty well. Even so, solution (1) works better so that is what you should do. * In the previous paragraph "It's not 100%" means that SQLite is not always successful in skipping intermediate blocks. It does always get the correct answer, 100% of the time. -- D. Richard Hipp drh at sqlite.org
