Multi-Column WHERE: Use an Index, Filter the Rest

Real queries rarely have a single test. WHERE name = 'Alice' AND age > 20 has two. The previous planner only looked at a WHERE that was exactly one equality, so this one scanned the whole table even though name has an index. A better planner notices that the AND makes the query easier: a row must satisfy every part, so any one part that an index can answer already narrows the search, and the other parts can be checked afterwards on the few rows that came back.

Split the WHERE into conjuncts

The tree you built for WHERE has AND nodes. The conjuncts of a condition are the pieces that are all required: flatten the chain of ANDs, left to right.

name = 'Alice' AND (age > 20 AND city = 'Oslo') AND NOT banned = 1
  -> name = 'Alice'   |   age > 20   |   city = 'Oslo'   |   NOT banned = 1

An OR or a NOT is not split: a = 1 OR b = 2 is one conjunct, because neither side alone is required. That is why OR queries cannot use an index here: if a = 1 has an index, rows with b = 2 are not in it.

Pick one conjunct to drive the lookup

A conjunct can drive a lookup when it has the form column = value (either way round) and:

• the column is the hidden rowid and the value is an integer; or
• the column has an index and the value is not NULL.

If several conjuncts qualify, the rules are: a rowid lookup beats an index lookup (it can match at most one row); otherwise the leftmost conjunct wins. Each pick is a simple, predictable rule; a real planner would weigh costs, and the next lesson shows how.

Every other conjunct becomes the filter: it is evaluated on each candidate row. The access path remembers these as its rest. Correctness never depends on the choice, since the whole WHERE is still checked on every candidate; the choice only decides how many candidates there are, which .examined reports.

Reading the new plans

A plan is the access line, then FILTER (...) if conjuncts remain:

SEARCH TABLE u USING INDEX idx_name (name='Alice') FILTER (age>20)
SEARCH TABLE u USING INDEX (rowid=1) FILTER (name='A' AND age=30)
SCAN TABLE t FILTER (id=1 AND k='x')
SCAN TABLE t

To print a filter you need to turn expression trees back into text, which is also a good test of whether you understood the tree:

• a comparison is written without spaces, exactly in the order given: age>20, name='Bob', 10<n; text literals are quoted and a quote inside is doubled; <> is written !=;
• x IS NULL and x IS NOT NULL keep their spaces;
• NOT x; and a AND b, a OR b with spaces;
• an AND or OR inside another AND, OR or NOT is wrapped in parentheses, so the text reads back as the same tree: NOT (b=1 OR c=2), (b=1 OR b=2) AND c>3;
• a single OR conjunct is printed as it is, with only the parentheses of FILTER ( ): FILTER (a=1 OR b=2).

What you build

The starter parses queries, runs them through the chosen path and prints plans. You write:

• split_conjuncts: flatten the AND chain.
• choose_access: choose the conjunct that drives the lookup, and collect the rest.
• unparse: write a condition back as text.

Common mistakes

• Dropping the driving conjunct but also the filter (or the reverse). The driving test must be removed from the filter, every other conjunct must stay.
• Splitting an OR. a = 1 OR b = 2 may not use the index on a.
• Letting column = NULL drive an index lookup. It is never true, so the plan would be misleading.
• Choosing the rightmost or an arbitrary index when two qualify.

Your exercise

You implement unparse, split_conjuncts and choose_access. The mistake to avoid: treating a WHERE that merely contains an indexed equality as if it were only that equality.