📎 This article includes 2 downloadable practice files ↓
An index is like the index at the back of a book: instead of reading every page (a scan), the database jumps straight to the rows (a search). On 300 rows you won’t notice; on 30 lakh rows it’s seconds against minutes.
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 5;
-- SCAN orders
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 5;
-- SEARCH orders USING INDEX idx_orders_customer (customer_id=?)Composite and covering indexes
An index on (customer_id, order_date) serves “this customer’s orders since July”. If the query needs only columns that are in the index, the database never touches the table: a covering index.
Write queries the index can use
| Slow (scan) | Fast (search) |
|---|---|
WHERE substr(order_date,1,7) = '2026-07' |
WHERE order_date >= '2026-07-01' AND order_date < '2026-08-01' |
WHERE YEAR(order_date) = 2026 |
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' |
WHERE name LIKE '%Traders' |
WHERE name LIKE 'Sharma%' (prefix only) |
Other databases: PostgreSQL EXPLAIN ANALYZE, MySQL EXPLAIN, SQL Server “Display Estimated Execution Plan” (Ctrl+L).
Practice
Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.
📎 Practice files for this article
⬇ Download all 2 files (ZIP · 9 KB)
- 📄Practice database (SQLite)shop2.db: customers, products, 300 orders, payments, employees with managers and monthly targets.⬇ DB · 48 KB
- 🗃️Lesson 9 exercisesTasks with the real expected result under each, and answers at the bottom.⬇ SQL · 2 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.