How can a SQLite composite index support a multi-column lookup?

A composite index is one index containing multiple columns in a chosen order. SQLite organizes entries by the leftmost indexed column first, then uses the next column to order entries that tie on the first. So (fruit, state) and (state, fruit) are different index arrangements, not interchangeable spellings. The order matters because the lookup patterns an index can support efficiently depend on which column leads it. [S1]

For example, assume a table named produce has fruit and state columns. These are alternative designs, not instructions to create both:

CREATE INDEX idx_produce_fruit_state ON produce(fruit, state);
-- Alternative if the intended access pattern starts with state:
CREATE INDEX idx_produce_state_fruit ON produce(state, fruit);

The right choice depends on the lookups the application actually needs.

Start by writing down the intended access patterns, not by guessing which column is more important. If the workload needs lookups beginning with fruit, (fruit, state) is a candidate. If it needs lookups beginning with state, (state, fruit) is a candidate. SQLite’s documentation explains that a multi-column index can use both columns to narrow a lookup, while reversing their order changes the index’s organization. [S1]

A planning illustration:

Needed lookup pattern              Candidate order to test
fruit and state together           (fruit, state) or (state, fruit)
fruit is the leading lookup need   (fruit, state)
state is the leading lookup need   (state, fruit)

This is a shortlist for testing, not a promise about performance or a rule that one order is always best. If the workload needs both single-column leading patterns, compare the options against those actual needs rather than adding extra indexes automatically.

Inspect the plan for the actual lookup with EXPLAIN QUERY PLAN. Its output can show a SEARCH using an index and indicate which constraints are used for that search. This helps you check whether SQLite selected the candidate and which indexed terms it applied; it is an inspection aid, not a guarantee that a particular index will be chosen. [S2]

Use the real statement and representative conditions from your application when you inspect the plan. A schematic result might identify a search using a named index and list terms for fruit and state, but that is only an illustration—not a predicted result for your database. The plan depends on the query and planner decisions. SQLite also notes that the textual output format may change between releases, so don’t make application behavior depend on exact plan text. [S2]

Use a short compare-and-inspect process. For the hypothetical produce table, note whether your real lookups use both columns together, start with fruit, start with state, or include other patterns. Choose a candidate order for the pattern you want to support, then inspect the plan for the actual lookup. If comparing both orders, assess them against the same workload and record what the plan reports; don’t infer a result just from the index definition.

Keep the scope clear: a plan shows SQLite’s chosen strategy for the inspected statement, not a universal guarantee about every statement or a measured speedup. Column order affects index organization, and plan inspection can reveal the chosen strategy and terms used. Neither tells you, without examining your workload, which order is best for your data. [S1] [S2]