On 29 Oct 2015, at 2:29pm, Jason H <jhihn at gmx.com> wrote: > In college databases, we calculated the estimated number of blocks (512-byte > device blocks) read given schema and statistics. Now, I'm asking how would > SQLite actually do that?
SQLite uses a block structure in its database files, but one file block is not always the same size as a storage-subsystem block. If you're okay with that, then the answer is to do it at the VFS level as Dominique described. > I am particularly interested in knowing if SQLite can skip the reading of > columns (and their disk blocks) not used in the query. that is, would Q1 read > on order of 128 blocks, or 16384 blocks (the whole table). And in Q2, 256 > blocks, or 16384 blocks (the whole table again, irrespective of the previous > statement and caches). Under what scenarios does the behavior change > (indexes?)
