Indexes and query plans

An index is a sorted structure that lets the engine find rows without reading the whole table. The query planner decides whether to use it.

CREATE INDEX lessons_course_minutes_idx
ON lessons (course_slug, minutes);

EXPLAIN QUERY PLAN
SELECT title, minutes
FROM lessons
WHERE course_slug = 'css' AND minutes > 12
ORDER BY minutes DESC;

• SEARCH means the engine used an index to narrow the rows.
• SCAN means it read the table, which is fine for small tables.
• A composite index should list equality columns first, then the sort column.
• Indexes speed reads but add work to every insert, update and delete.