How the SQLite Virtual Machine Works

Summary

SQLite processes SQL through a parse-optimize-execute pipeline that turns query text into executable virtual-machine instructions. It tokenizes characters, parses them into an AST such as a Select struct, and uses table statistics to choose between a full table scan and index-based access. The source describes sqlite_stat1's coarse index and duplicate-value counts alongside sqlite_stat4's distributed samples, then traces bytecode that opens the persons table, compares favorite_color with "blue", and returns matching columns. Registers hold intermediate values, cursors iterate through tables, and sqlite3_step() resumes execution after each returned row; the VM has 186 opcodes as of writing, while bind parameters allow prepared statements to skip repeated parsing and optimization.

Takeaways

1. 1

   SQLite's parser tokenizes SQL and builds an AST represented by structures such as Select before query planning begins.

2. 2

   sqlite_stat1 stores coarse index statistics, while sqlite_stat4 stores samples distributed across an index to help estimate value uniqueness.

3. 3

   The VM executes bytecode through registers and cursors; bind parameters let prepared statements reuse compiled work and avoid repeated parsing and optimization.