A database index is an auxiliary data structure maintained by a database management system to make certain data-retrieval and data-manipulation operations more efficient. It works somewhat like an index in a reference book: instead of examining every row to find matching information, the database can use the index to locate relevant rows more directly.
Common relational database indexes are implemented using structures such as B-trees or B+ trees, while specialised indexes may use hash structures, inverted indexes, spatial structures or other algorithms. An index usually contains indexed key values together with information that allows the database engine to locate the corresponding table rows. The exact implementation is database-specific and normally hidden from ordinary SQL users.
Indexes can substantially improve queries that filter, join, sort or enforce uniqueness on indexed columns. Suppose a table contains millions of customer rows and a query searches for a particular customer_id. Without a suitable index, the database might need to scan a large portion of the table. With an index, it can often navigate the index structure to locate the relevant row much more efficiently.
Indexes can also support range searches, such as finding records where a timestamp falls between two dates, depending on the index type. The improvement is workload-dependent rather than guaranteed; an optimiser may decide that a sequential scan is cheaper when a query needs a large proportion of the table.
A common index structure is a B-tree family structure, which maintains keys in an ordered arrangement and supports efficient equality and range operations. B-tree indexes can help queries involving conditions such as greater-than, less-than, BETWEEN and ordered retrieval. Hash indexes are designed primarily around equality lookups in systems that support them, while full-text indexes use specialised structures for searching textual content. Spatial indexes support geographical or geometric queries. Bitmap indexes can be effective for particular analytical workloads with low-cardinality columns, although they are not universally suitable. The index type should therefore correspond to the query patterns and data characteristics.
Indexes have costs as well as benefits. Every index consumes storage, and the database must maintain the index when indexed data changes. INSERT operations may require new index entries, while UPDATE operations can require existing entries to be modified or relocated. DELETE operations must remove corresponding entries. A table with many indexes can therefore experience slower write performance and increased storage consumption. Indexes can also become fragmented or require maintenance depending on the database engine and workload. Database design should consequently use indexes where they provide meaningful benefits rather than indiscriminately indexing every column.
A unique index or index-backed constraint can enforce uniqueness. Primary keys and UNIQUE constraints commonly have indexes associated with them so that duplicate values can be detected efficiently. A composite index covers multiple columns in a defined order. For example, an index on customer_id and order_date can be useful for queries filtering by customer_id and then working with order dates, but its usefulness can differ for queries that filter only by order_date. This relates to the leftmost-prefix behaviour of many B-tree composite indexes, although exact optimiser capabilities vary. The order of columns in a composite index therefore matters.
Indexes can also be covering indexes, meaning that the index contains all information needed for a particular query, allowing the database to obtain results without accessing the base table for every row. Some database systems support included or non-key columns for this purpose. Covering indexes can reduce I/O for selected workloads but increase index size. Partial or filtered indexes can index only rows satisfying a condition where supported, reducing index size and targeting specific queries. Functional or expression indexes can index calculated expressions rather than raw column values. These specialised forms allow index design to closely match application query patterns.
The database optimiser determines whether and how to use available indexes. It may estimate the cost of an index lookup, table scan, join strategy and other operations using statistics about data distribution. An index can exist and still be ignored if the optimiser determines that another approach is cheaper. Poor or outdated statistics can lead to inappropriate execution plans. Query performance should therefore be measured using execution plans and realistic workloads rather than assuming that adding an index automatically solves a performance problem. Excessive indexing can even degrade overall performance by increasing write and maintenance costs.
A database index is not a second authoritative copy of the table in the conceptual sense, although it contains duplicated information necessary to accelerate access. If an index becomes damaged, a database system can generally rebuild it from the underlying table data, subject to the engine's recovery mechanisms. Indexes therefore primarily optimise access rather than provide backup or data-recovery protection. They should not be confused with primary keys, although primary-key constraints are often supported by indexes. Effective indexing is a balance between read performance, write overhead, storage, query patterns, data distribution and database-engine behaviour.
