Database Indexes: Turn a Full Scan Into an Instant Lookup
A query that feels instant on 1,000 rows can crawl on 1,000,000. The usual reason: the database is doing a full table scan — reading every row to find the few you asked for. An index fixes that.
Without an index: a full table scan
SELECT * FROM users WHERE email = '[email protected]';
With no index on email, the engine reads every row and compares email. On a large table that is O(n) work.
With an index: a lookup
CREATE INDEX idx_users_email ON users (email);
An index is a sorted structure (usually a B-tree). The engine now navigates straight to the matching rows in about O(log n) — a handful of reads instead of millions.
Read the plan with EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
type = ALL(and "Using where") means a full scan — slow.type = reforconstwith akeylisted means the index is used — good.
Composite indexes follow a left-to-right rule
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
This helps WHERE user_id = ? and WHERE user_id = ? AND status = ?, but not WHERE status = ? on its own — a query must use a left prefix of the indexed columns.
When an index does NOT help
WHERE YEAR(created_at) = 2026— wrapping the column in a function defeats the index. Use a range instead:created_at >= '2026-01-01' AND created_at < '2027-01-01'.LIKE '%term'with a leading wildcard cannot use a normal index.- Very low-selectivity columns (like a boolean) — a scan is sometimes cheaper than the index.
Key takeaways
- No index means a full scan (O(n)); the right index means a lookup (O(log n)).
- Index the columns you filter, join and sort on — not every column (indexes cost writes and storage).
- Use
EXPLAINto confirm the index is actually used. - Keep filters sargable: never wrap an indexed column in a function.
Thử thách
Luyện tập ngay điều vừa học. Viết lời giải, mở gợi ý nếu bí.
Đề bài
This query filters on YEAR(created_at), which wraps the indexed column in a function and forces a full table scan even when created_at is indexed. Rewrite it as a sargable date-range query that can actually use the index.
Code khởi tạo
SELECT * FROM orders
WHERE YEAR(created_at) = 2026;Lời giải của bạn
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';