An index is an ordered or hashed access structure that helps the optimizer locate candidate rows without reading every table page. This lesson is for learners who can write SELECT queries and want to understand why an index helps one predicate, is ignored for another, or makes writes more expensive.
You will learn to choose column order, recognize selective and nonselective predicates, read an execution plan, and remove indexes whose maintenance cost is not justified by measured workload benefit.
Without an index, a database must scan every row in a table to find matching records (full table scan). For large tables with millions of rows, this is extremely slow. An index is a data structure that allows the database to find rows much faster - similar to a book's index that lets you jump directly to a topic instead of reading every page.
Trade-offs:
| Type | Description | Pros | Cons |
|---|---|---|---|
| Dense Index | An index entry for every record in the data file | Faster search (direct lookup) | More storage space |
| Sparse Index | Index entries only for some records (e.g., one per block) | Less storage space | Requires sequential scan within block |
| Index Type | Description | Use Case |
|---|---|---|
| Primary Index | Built on the primary key. Data file is ordered by the key. One entry per block (sparse). | Fast lookup by primary key |
| Secondary Index | Built on non-primary key attributes. Data file may not be ordered by this key. Dense index. | Fast lookup by non-key attributes |
| Clustering Index | Built on a non-key attribute that orders the data file. One entry per distinct value. | Range queries on ordered non-key fields |
| Unique Index | Ensures all values in the indexed column are unique. | Enforce uniqueness on non-PK columns |
| Composite Index | Index on multiple columns. | Queries filtering on multiple columns |
| Full-Text Index | Optimized for text search operations. | Language-aware word and phrase search |
The most common index structure in relational databases is the B+ Tree:
Why B+ Tree is preferred:
A hash index uses a hash function to map key values to bucket addresses. It provides O(1) average-case lookup for equality queries.
A bitmap index uses a bit array (bitmap) for each distinct value of an attribute. Each bit represents whether a row has that value.
Cardinality is the number of distinct values in a column or column combination. Selectivity describes how narrowly a predicate filters the table. A unique email lookup is highly selective because it should return at most one row; a status = active predicate may be weakly selective when almost every row is active.
An index lookup has startup work and often requires extra table-page reads. When a predicate returns a large fraction of the table, sequentially reading table pages can cost less than jumping between an index and many scattered rows. The optimizer estimates that crossover from statistics, data distribution, row width, and storage costs. An existing index is therefore an option, not an instruction.
A single distinct-count value cannot describe skew. If 99 percent of orders are complete and 1 percent are pending, the same status index may be useful for pending but wasteful for complete. Histograms or frequency statistics help the optimizer distinguish those values. Refresh statistics after major data changes when estimates no longer resemble actual row counts.
A composite B-tree index is sorted lexicographically: first by its leading column, then by the next column within equal leading values. An index on (customer_id, ordered_at) can efficiently find one customer and then scan that customer's date range. A date-only query may not have a contiguous starting range because every customer owns a separate date sequence.
The useful-prefix rule is more precise than saying every query must filter the first column. Some engines can scan the whole index, use skip-scan-like strategies, or use it for covering data, but those alternatives may be costly. Choose order from the workload: equality columns commonly lead, followed by range or ordering columns, while selectivity and sort requirements can change the decision.
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, ordered_at);
SELECT order_id, ordered_at, total_amount
FROM orders
WHERE customer_id = 42
AND ordered_at >= '2026-01-01'
ORDER BY ordered_at;The index provides a narrow customer range already ordered by ordered_at; the exact plan remains optimizer- and data-dependent.A covering index contains every column needed by a query, allowing an index-only access path when the engine can confirm row visibility without fetching the base row. Key columns control search order; included or payload columns, where supported, can cover output without changing that order. Wider indexes consume more storage and create more write and cache pressure, so coverage should serve an important repeated query rather than every possible SELECT list.
A partial or filtered index stores only rows satisfying a predicate, such as open orders. It can be smaller and more selective than indexing the entire status column, but the query predicate must imply the index condition. Product syntax and matching rules differ, so inspect the actual plan instead of assuming the index qualifies.
A search argument is sargable when the engine can translate it into an index range. Wrapping an indexed column in a function, applying an implicit type conversion, or beginning a pattern with a wildcard can hide a useful range. For example, comparing DATE(created_at) to one day often prevents a normal index range; comparing created_at with a start timestamp and the next day's timestamp exposes clear lower and upper bounds.
-- Harder to use with a normal index on created_at
WHERE DATE(created_at) = '2026-07-13'
-- Sargable half-open range
WHERE created_at >= '2026-07-13 00:00:00'
AND created_at < '2026-07-14 00:00:00'Start with the slow query and representative parameters, not with a guessed index. Capture its plan, estimated rows, actual rows when safe, elapsed time, logical reads, sort or hash spills, and rows removed by filters. A large estimate-versus-actual mismatch points toward stale statistics, correlated columns, parameter sensitivity, or a predicate the estimator cannot model well.
After adding an index, repeat the same measurement under realistic cache and concurrency conditions. Check write latency and storage growth as well as read speed. Remove redundant indexes only after comparing key order, uniqueness, included columns, constraints, and workload use; two similarly named indexes may enforce different guarantees.
| Plan Signal | Likely Meaning | Next Check |
|---|---|---|
| Table scan with few output rows | No useful access path or hidden predicate | Sargability, type conversion, candidate index |
| Index lookup with many table fetches | Predicate is not selective or index does not cover | Returned fraction and covering trade-off |
| Large estimate error | Statistics or data correlation problem | Refresh and inspect distribution |
| Sort despite an index | Index order differs from requested order | Equality prefix and sort direction |
| Fast reads but slow writes | Too many or overly wide indexes | Usage statistics and write workload |
CREATE TABLE lesson_dbms (
id INT PRIMARY KEY,
description VARCHAR(120),
amount DECIMAL(10,2),
status VARCHAR(20)
);
INSERT INTO lesson_dbms VALUES
(1, 'DBMS normal case', 1000.00, 'active'),
(2, 'DBMS boundary case', 0.00, 'review');
SELECT * FROM lesson_dbms;BEGIN;
UPDATE lesson_dbms
SET status = 'checked'
WHERE amount >= 0;
SELECT status, COUNT(*) AS rows_seen
FROM lesson_dbms
GROUP BY status;
ROLLBACK;
-- Explanation: ROLLBACK lets you test the concept safely before committing changes.Every index is another data structure the DBMS must maintain. An INSERT adds entries to each relevant index, while an UPDATE may remove and add entries when indexed columns change.
B-tree composite indexes are most effective from their leftmost columns. An index ordered by department_id and then salary groups salaries inside each department, so a salary-only filter may not have a useful starting point.
The optimizer estimates whether using the index costs less than scanning the table. It may prefer a scan when the query returns a large percentage of rows, statistics are stale, a function hides the indexed column, types require conversion, or the index order does not match the predicate.
Explore 500+ free tutorials across 20+ languages and frameworks.