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.
