SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 173 of 250

Easy

Read an indexed search

Your task

Explain the query that selects title from catalog for genre 'Jazz' and price 25.

SEARCH indicates a lookup that narrows the visited rows. The catalog has an index on genre and price.

Sample data

Table:catalog
idtitlegenrepricestock
1Record 0001Indie111
2Record 0002Ambient122
3Record 0003Rock133
4Record 0004Jazz144
5Record 0005Indie155
6Record 0006Ambient166
7Record 0007Rock177
8Record 0008Jazz188
9Record 0009Indie190
10Record 0010Ambient201

Preview of 10 of 2048 rows

Expected output

idparentnotuseddetail
3061SEARCH catalog USING INDEX idx_catalog_genre_price (genre=? AND price=?)
Show a hint

Use EXPLAIN QUERY PLAN with equality on genre and price.

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;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…