Queries and indexes

A SELECT reads rows, filters them with WHERE, and orders them with ORDER BY. An index helps SQLite find matching rows without inspecting every row. It also takes storage and adds work to writes.

Create an index and inspect the plan

For the notes table in tables and values:

CREATE INDEX notes_body_idx ON notes(body);

SELECT id, body FROM notes WHERE body = 'Buy milk';
EXPLAIN QUERY PLAN SELECT id, body FROM notes WHERE body = 'Buy milk';

SQLite's query planner chooses whether to use an index. Inspect the plan with representative data before adding indexes for queries your application runs. See SQLite's query planning guide.

Parameters and prepared statements

A prepared statement keeps SQL compiled for execution. Parameters such as ? keep values separate from that SQL, so the same statement can run with different values. Parameters represent values, not table or column names. See SQLite's expression documentation.

Query spatial ranges

An R*Tree virtual table indexes bounding boxes for spatial or interval searches. A normal B-tree index orders values by its columns; an R*Tree narrows searches across several coordinate ranges.

CREATE VIRTUAL TABLE place_bounds USING rtree(
  id,
  minLongitude, maxLongitude,
  minLatitude, maxLatitude
);

INSERT INTO place_bounds VALUES (1, 16.36, 16.38, 48.2, 48.22);

SELECT id FROM place_bounds
WHERE minLongitude <= 16.4 AND maxLongitude >= 16.35
  AND minLatitude <= 48.23 AND maxLatitude >= 48.19;

The query returns 1. Bounding boxes can include points outside an object's exact geometry, so apply an exact check when needed. Keep boxes in sync with the source data through triggers or updates in the same transaction.

rtree rounds 32-bit floating-point bounds outward. Overlap queries can return extra candidates, while containment queries may need an expanded query box. rtree_i32 uses signed 32-bit integer coordinates. Both support one to five dimensions. See SQLite's R*Tree documentation.

Finish an R*Tree scan before modifying its table; changes to the scan's index can fail with SQLITE_LOCKED.

For Nitro SQLite, see parameters and results to run queries, performance to bound database work, and Configuration for R*Tree availability.