An index is an additional structure that helps a database locate rows without inspecting every record. It resembles a sorted lookup guide: the database follows searchable values to the corresponding table rows. That guide consumes space and must be maintained when data changes.
The original index lesson introduces the basic idea. This reference concentrates on choosing a structure and a column order for an actual query. “Add indexes to every column” is a poor plan because indexes speed up some reads while making inserts, updates and deletes do more work. Start from a frequent query and its filters, joins and sorting requirements.
B-tree, hash and specialized indexes
B-tree indexes keep searchable values in an ordered tree structure. They commonly support equality searches, ranges and useful ordering. PostgreSQL, MySQL, SQL Server and SQLite all use tree-based structures for ordinary indexes, though their storage details and available options differ.
Hash indexes focus on equality lookups. Their availability depends on the engine and storage system; they are not a general substitute for ordered indexes when a query needs a range or sorted results. Do not choose a type based only on the word “fast” in a comparison chart.
Text search, geographic queries and other specialized operations can use specialized indexes. PostgreSQL has several access methods, SQL Server offers columnstore and full-text facilities, and MySQL offers full-text and spatial indexes under particular conditions. SQLite extensions can provide their own search structures. These are features to select for a specific workload, not portable decorations on every table.
Primary and unique constraints often create supporting indexes automatically. Inspect the existing schema before adding another index on the same key. Two equivalent structures can waste disk and maintenance effort while leaving the actual slow query unchanged.
Column order and the leading prefix
A composite index contains multiple columns in a defined order. An index on (customer_id, created_day) naturally groups entries by customer, then orders that customer's entries by day. An index on (created_day, customer_id) organizes the same values differently and serves a different set of query patterns.
Leading columns are especially important. A customer equality condition followed by a date range is a common reason to use customer first. A query that filters only by date may not get the same benefit from that index. Some optimizers can use skip scans or other strategies, but those are not a promise that column order never matters.
After a range condition, later columns may still help filter or cover a query, but they do not automatically narrow one contiguous search interval in the same way as earlier equality columns. Evaluate the actual engine plan instead of treating a simplified prefix rule as an absolute description of every optimizer feature.
Choose column order from frequent workload patterns. If two important queries require different leading columns, separate indexes may be justified. Compare their combined read benefit against the additional write and storage cost.
Covering indexes and storage differences
A covering index contains all values needed by a particular query, potentially avoiding separate table lookups. Coverage is a property of the index and query together: changing the selected columns can make a previously covering index insufficient.
PostgreSQL and SQL Server support included-column syntax, while MySQL and SQLite have different ways of achieving coverage through indexed columns and their row identifiers. Do not copy INCLUDE syntax into engines that do not support it. PostgreSQL index-only scans also depend on visibility information, so containing every selected column does not guarantee that every execution avoids table access.
Clustered storage is another engine-specific concept. InnoDB stores table rows organized by the primary key; SQL Server can have a clustered index with different design choices. PostgreSQL's CLUSTER operation does not maintain permanent clustered order as later writes arrive. SQLite's rowid and WITHOUT ROWID tables have their own organization.
Wide strings and large payloads can make indexes expensive. Avoid adding every output column merely to earn the label “covering.” Measure whether fewer row lookups justify a larger structure for the workload you actually run.
Practice: verify an index decision
Write down two queries: one reads a customer's recent orders, and the other reads all orders from a particular day. Predict which is better supported by the customer-first index in the example. Then inspect each query's execution plan in your engine.
On a three-row table, a full scan may be the sensible choice. To study performance, create representative data in a sandbox, update statistics and compare plans and timings. Record the query, row count, selected columns and parameter values so another developer can repeat the observation.
Before removing an index, check other queries and constraints that depend on it. Before adding one to production, consider build time and locking behavior. The practical solution is a measured change associated with a specific query pattern, not an assumption that every scan indicates a missing index.
Include the application's write pattern in that decision. An archive table read repeatedly and changed rarely can justify different indexes from a queue receiving constant inserts. A useful index is one whose measured benefit is worth its maintenance under the actual workload.
Further reading
Read the PostgreSQL index guide for its supported methods and engine-specific options.
SQL Index Types and Composite Indexes Examples
Each example shows the code, the output it produces and what each part does.
Match a composite index to a query
Use a disposable practice table. The following index and query use ordinary SQL supported by the four database families in this course.
The index stores customer first, then day within each customer. The query selects one customer and searches a date range inside that customer's records. The returned values are the same with or without the index; an index is intended to change the access work, not the meaning of the query.
CREATE TABLE index_orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
created_day VARCHAR(10) NOT NULL,
amount INTEGER NOT NULL
);
INSERT INTO index_orders VALUES
(1, 7, '2026-10-01', 20),
(2, 8, '2026-10-02', 40),
(3, 7, '2026-10-03', 30);
CREATE INDEX idx_orders_customer_day
ON index_orders(customer_id, created_day);
SELECT order_id, created_day, amount
FROM index_orders
WHERE customer_id = 7
AND created_day >= '2026-10-02'
ORDER BY created_day, order_id;order_id | created_day | amount
3 | 2026-10-03 | 30SQL Index Types and Composite Indexes FAQ
Can an index make writes slower?
Yes. Each relevant insert, update or delete must maintain the index as well as the table, and the index also uses storage. That's why you add indexes for real query needs rather than on every column.
Does an index guarantee sorted output?
No. SQL output order requires an explicit ORDER BY, even when the chosen access path happens to follow index order.
Why was my index ignored?
The optimizer may estimate that another plan is cheaper, especially for small tables or filters that match much of the data. Check statistics and the complete query.
What is a covering index?
An index that contains every column a query needs, so the database can answer from the index alone without visiting the table. PostgreSQL and SQL Server let you add non-key columns with INCLUDE; in MySQL's InnoDB, secondary indexes automatically carry the primary key.
When is a hash index useful?
Hash indexes only support equality lookups, such as WHERE token = ?, not ranges or sorting. PostgreSQL offers them explicitly, but B-tree indexes handle equality almost as well and support much more, so B-tree remains the sensible default.
SQL Index Types and Composite Indexes Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which query best matches an ordinary index on (customer_id, created_day)?
SQL Index Types and Composite Indexes Quiz
2 questions to check what you learned.
What should adding a normal index change?
- The correct result rows of every SELECT
- The possible access paths and maintenance work
- All NULL values into zero
When is an index covering?
- Whenever it contains at least two columns
- Whenever its name starts with idx
- When it supplies all values needed by a particular query