Table Scan: What a Query Costs

After this lesson the engine can describe how it will run a query (.explain), say how many rows the last query had to look at (.examined) and count a table's rows (.count). None of these is hard to code. They matter because they give you a ruler, and the rest of the course is about making that ruler's numbers smaller.

What your SELECT has been doing

Every SELECT so far has run the same loop:

for each stored row, first to last:
    evaluate the WHERE clause
    if it is true: project the columns and print the line

This is a full table scan, the only access method a table has until it gets an index. Its cost depends on one thing, the number of rows in the table:

There is no early exit, because the engine cannot know that no later row matches. A scan is O(n) in the table size, not in the result size. The cost is paid in visited rows, and printing is separate.

.examined: the cost, observed

The SELECT driver already records, as it runs, how many rows the scan visited. .examined prints that number for the most recent successful SELECT:

• before any SELECT it is 0
• a SELECT that fails (unknown table or column) does not change it
• .explain and .count never change it, because they do not scan

Later lessons add an index; then a one-row lookup will visit one row and this number will show the difference.

.explain: the plan without the run

.explain <statement> prints the plan the engine would use and does not execute the statement. With only scans available there is exactly one plan:

.explain SELECT * FROM users WHERE age > 25
SCAN TABLE users

To produce it, tokenize the text after .explain, check that it is a SELECT, find the table name that follows FROM, and check that the table exists. Everything else about the statement is ignored here: an unknown column in WHERE is not reported by .explain. Errors you do report: a missing statement (ERR usage: .explain <select statement>), a statement that is not a SELECT or has no table (ERR syntax error), a lexer problem (its own message) and an unknown table (ERR unknown table: <name>).

Real planners do the same job: PostgreSQL has EXPLAIN, SQLite has EXPLAIN QUERY PLAN, whose scan line reads SCAN users. The format here is simply yours; the lesson is that a plan can be inspected without running a query.

.count: metadata, not a scan

.count <table> prints the number of rows. The engine already knows that number without visiting a single row, because a table stores its rows in a list. This is the difference between asking how many rows exist and how many satisfy a condition. The first is bookkeeping; the second needs the scan. An empty table prints 0. Rejected inserts add nothing to the count.

Common mistakes

• Counting by running a SELECT. .count must not change .examined.
• Updating .examined when a query fails. Only a query that actually scanned counts.
• Executing the statement in .explain. It must not print result rows.
• Printing the plan for a table that does not exist.

Your exercise

The starter has everything else, including the scan that fills the examined counter. You write cmd_count, cmd_explain and cmd_examined. The mistake to avoid: reading the examined number from anything other than what the last SELECT recorded.