Database Indexing is the creation and use of auxiliary data structures that allow a database management system to locate qualifying rows more efficiently than scanning an entire table in many query patterns. An index generally stores values derived from one or more table columns together with references or identifiers that allow the system to locate the corresponding records.
The exact structure depends on the database engine and index type, but common implementations include B-tree or B+ tree structures, hash indexes, bitmap indexes and specialised structures for full-text, spatial, array or JSON-oriented workloads. An index is analogous to an organised lookup structure rather than a duplicate table in the conceptual data model. The query optimiser considers available indexes, estimated selectivity, table statistics, predicates, joins, ordering requirements and expected costs when deciding whether an index should be used. An index therefore does not automatically improve every query, and an index that is theoretically relevant can be rejected when the optimiser estimates that another access path is cheaper.
B-tree-family indexes are among the most widely used general-purpose structures because their ordered representation supports equality searches, range predicates and ordered retrieval. A search can traverse the tree from its root through progressively narrower branches until the relevant leaf entries are reached, rather than examining every row. Leaf-level organisation can also facilitate range scans and ordered operations.
Hash-based indexes instead use a hash function to map keys into buckets and are particularly suited to equality lookups, although their capabilities for ordered range access differ fundamentally from tree-based structures. Bitmap indexes represent sets of row membership through bitmaps and can be highly effective for certain low-cardinality analytical workloads, but their update characteristics and implementation details make them less universally suitable for high-write transactional systems. Specialised database systems may provide inverted indexes for text, R-tree-like structures for spatial data or expression-based indexes for computed values.
Index selectivity is a central consideration in Database Indexing. Selectivity describes how effectively a predicate narrows the candidate rows, although precise definitions and optimiser statistics vary between systems. An index on a column with many distinct values can often provide substantial filtering for equality predicates, whereas an index on a column containing only a few possible values may be less useful for a query that selects a large percentage of the table.
However, low-cardinality indexes are not universally useless: their usefulness depends on data distribution, query predicates, table size, clustering, index representation and workload. Composite indexes can support queries involving multiple columns, and column order is significant because the database may exploit the leading portion of the indexed key more effectively than later columns when evaluating certain predicates. A carefully chosen composite index can also support sorting or grouping, reducing the need for a separate sort operation.
Indexes have costs as well as benefits. An index consumes storage and must generally be maintained when indexed rows are inserted, deleted or modified. Updating an indexed column may require changes to the index structure, potentially increasing write amplification and contributing to page splits, fragmentation or other maintenance work depending on the engine. Having many overlapping indexes can therefore degrade write throughput and consume substantial disk and memory resources.
Indexes can also become less useful as data distributions change, particularly when statistics become stale or workload patterns evolve. Database administrators and developers consequently need to evaluate indexes against actual query workloads rather than creating one index for every frequently referenced column. Query plans, execution statistics, index usage statistics and workload measurements can reveal whether an index is actually providing benefit.
Different index designs serve different purposes. A unique index can enforce uniqueness while also providing an access path for lookups. A composite index contains multiple key columns and can support queries whose predicates align with its ordering. A covering index contains sufficient indexed information for a query to be answered without fetching the corresponding base-table rows, where the database engine supports such behaviour.
Partial or filtered indexes contain only rows satisfying a specified condition, reducing index size and maintenance overhead for suitable workloads. Expression or functional indexes index a computed expression rather than merely storing a raw column value, allowing certain predicates to become directly searchable. Clustered indexes, where supported, influence the physical or primary organisation of table records, while non-clustered indexes provide separate access structures. The terminology and implementation differ among database management systems, so an index feature should always be understood in the context of the specific engine.
Database Indexing interacts closely with query optimisation. A database optimiser estimates alternative execution plans using information such as table cardinality, value distributions, predicate selectivity, available indexes and join relationships. It may choose an index seek or range scan for a selective query, an index scan for a query requiring a substantial proportion of an index, or a full table scan when reading the table directly is expected to be cheaper. Indexes can also support join algorithms, uniqueness enforcement and ordering.
Poor cardinality estimates can cause the optimiser to choose an unsuitable plan even when an appropriate index exists. Conversely, an apparently inefficient full scan may be entirely reasonable for a small table or a query that returns most of its rows. Understanding indexing therefore requires distinguishing the existence of an index from the execution plan actually selected by the database.
Index design must account for query patterns rather than column popularity alone. A column being frequently selected in a SELECT clause does not necessarily justify indexing it, because an index primarily helps with access conditions, ordering, grouping, joins or covering requirements depending on the engine. Queries using functions or transformations on indexed columns may fail to exploit ordinary indexes unless the optimiser can transform the predicate or an expression index exists.
Leading-wildcard searches in ordinary ordered indexes are often difficult to optimise, whereas full-text indexes are designed for different search semantics. Parameterisation, implicit type conversion, collation and expression behaviour can also affect index usability. Index maintenance is similarly workload-dependent: a read-heavy application can tolerate more indexes than a write-intensive system, while analytical workloads may favour specialised indexing structures that would be inappropriate for a highly transactional workload.
Database Indexing is one component of performance engineering rather than a substitute for good schema design or efficient SQL. An index cannot compensate indefinitely for queries that process excessive data, return unnecessarily large result sets, use inefficient joins or apply non-selective predicates. Likewise, indexing every foreign key or every commonly searched field without workload analysis can produce unnecessary overhead.
Effective indexing involves identifying important queries, examining their execution plans, understanding cardinality and distribution, selecting appropriate key order and included data where applicable, testing with realistic datasets, and monitoring performance after deployment. Indexes should be revisited as applications evolve because a structure that was useful for one workload can become redundant or harmful after schema, data volume or query patterns change. The fundamental trade-off is that indexes exchange additional storage and write-maintenance work for potentially faster access to selected data.
