Constrain an indexed range
Your task
Explain a query returning titles of Indie catalog entries with prices from 20 through 25.
A composite index can narrow by an equality on its leading column and then by a range on the next column.
Sample data
catalog| id | title | genre | price | stock |
|---|---|---|---|---|
| 1 | Record 0001 | Indie | 11 | 1 |
| 2 | Record 0002 | Ambient | 12 | 2 |
| 3 | Record 0003 | Rock | 13 | 3 |
| 4 | Record 0004 | Jazz | 14 | 4 |
| 5 | Record 0005 | Indie | 15 | 5 |
| 6 | Record 0006 | Ambient | 16 | 6 |
| 7 | Record 0007 | Rock | 17 | 7 |
| 8 | Record 0008 | Jazz | 18 | 8 |
| 9 | Record 0009 | Indie | 19 | 0 |
| 10 | Record 0010 | Ambient | 20 | 1 |
Preview of 10 of 2048 rows
Expected output
| id | parent | notused | detail |
|---|---|---|---|
| 3 | 0 | 49 | SEARCH catalog USING INDEX idx_catalog_genre_price (genre=? AND price>? AND price<?) |
Show a hint
Use genre='Indie' AND price BETWEEN 20 AND 25.
Indexes and query plans guide
Read SCAN and SEARCH
EXPLAIN QUERY PLAN describes access paths. SCAN indicates a scan of a table or index. SEARCH indicates that SQLite can use constraints to narrow the rows it visits. The exact plan depends on indexes, the query, and the database contents.
EXPLAIN QUERY PLAN
SELECT title FROM catalog WHERE stock = 3;
EXPLAIN QUERY PLAN
SELECT title FROM catalog WHERE genre = 'Jazz' AND price = 25;Use a composite index from the leading column
The catalog index orders entries by genre and then by price. Filtering by genre and a price range lets SQLite narrow the search and read prices in index order. Selecting only genre and price can make this a covering lookup, which avoids reading extra columns from the table.
EXPLAIN QUERY PLAN
SELECT genre, price
FROM catalog
WHERE genre = 'Jazz' AND price >= 20
ORDER BY price;Look for a separate sort
The plan may report a temporary B-tree when no available access path provides the requested order. A scan can still use an index, so look at the whole plan. Indexes trade read improvements for extra storage and work on writes.
EXPLAIN QUERY PLAN
SELECT title FROM catalog ORDER BY title;Worked solution
EXPLAIN QUERY PLAN SELECT title FROM catalog WHERE genre='Indie' AND price BETWEEN 20 AND 25;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
