Index a case-insensitive title search
Your task
Create idx_catalog_title_nocase on title COLLATE NOCASE. Then show the query plan for selecting id where title matches 'record 0007' using COLLATE NOCASE. Require that index with INDEXED BY.
A text index with a named collation can support comparisons that use the same collation.
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 |
|---|---|---|---|
| 2 | 0 | 55 | SEARCH catalog USING COVERING INDEX idx_catalog_title_nocase (title=?) |
Show a hint
Use COLLATE NOCASE in the index key and the equality predicate.
Optimization and capstones guide
Compare equivalent filters
A function on an indexed column can hide the key from a direct range lookup. The two predicates here select the same January dates in this dataset. Compare the plans and look for a constrained SEARCH on the second query.
EXPLAIN QUERY PLAN
SELECT id FROM receipts WHERE STRFTIME('%Y-%m', sold_on) = '2025-01';
EXPLAIN QUERY PLAN
SELECT id FROM receipts
WHERE sold_on >= '2025-01-01' AND sold_on < '2025-02-01';Continue from a stable key
OFFSET skips entries before the requested page. With keyset pagination, use the last ID from the previous page as a boundary. The order and boundary must use the same key. Use a unique tie-breaker if the leading sort column can repeat.
EXPLAIN QUERY PLAN
SELECT id, title FROM catalog
WHERE id > 500
ORDER BY id
LIMIT 5;Summarize first, compare second
Start reports by deciding what one result row represents. This report groups receipts by month, then compares monthly totals with LAG. If February has no receipts, the next row compares with January. Generate a calendar CTE and left join it when gaps need to appear explicitly.
WITH monthly AS (
SELECT STRFTIME('%Y-%m', sold_on) AS month, SUM(amount) AS total
FROM receipts
GROUP BY month
)
SELECT month, total, total - LAG(total) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;Worked solution
CREATE INDEX idx_catalog_title_nocase ON catalog(title COLLATE NOCASE); EXPLAIN QUERY PLAN SELECT id FROM catalog INDEXED BY idx_catalog_title_nocase WHERE title='record 0007' COLLATE NOCASE;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
