On Thu, Oct 29, 2015 at 3:29 PM, Jason H <jhihn at gmx.com> wrote: > I'm trying to figure out how SQLite3 would read the schema and data off > the disk below. I read https://www.sqlite.org/fileformat2.html but didn't > find what I was looking for. > 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? > > Given this pseudo code: > create table aTable(c1 text, ..., c128 text); > for 1 to 100,000: > insert into aTable (data1, ..., data128); -- where each dataN is a random > length from 0 to 128k bytes, with an average of 64kb > > Now for the magic: > select data64 from aTable; -- Q1 > select data1, data128 from aTable; -- Q2 > > 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?) > > Many, many thanks in advance for this info. >
Depends on the page size, page cache size, vacuum options, etc... If you really want to know at that level of detail, then write your own shim VFS (http://www.sqlite.org/vfs.html) to know when/if SQLite reads and writes to disk. I think I remember seeing one of the graphical SQLite tools showing a "heat map" of DB pages, which at the time thought must be using a VFS to show that. --DD
