🔍Indexing

NEW

The use of auxiliary data structures to accelerate data retrieval operations, trading extra storage and write overhead for faster reads.

DESCRIPTION

Indexes speed up lookups by organizing references to rows according to key values. Common structures include B-trees for range scans, hash indexes for equality, and bitmap indexes for low-cardinality analytics. Indexes improve read performance but add write costs and storage overhead. Proper indexing considers query patterns, selectivity, and maintenance. Composite, covering, and partial indexes further optimize workloads. Misuse can degrade performance and complicate maintenance.

ALTERNATIVE DEFINITIONS

Wikipedia

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.

Explicitly notes the read-write trade-off characteristic of indexes.

Source

PostgreSQL

Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

Practical, vendor documentation highlighting purpose and usage.

Source

Oracle

An index is an optional structure, associated with a table, that can speed up access to rows of the table.

Defines index as optional and focused on access speed.

Source

RELATED TERMS

B-Tree Hash Index Bitmap Index Full-Text Search Clustered Index Nonclustered Index Cardinality Covering Index Primary Key Query Planner

COMPETING TERMS

Table Scan

RELATIONS

• Narrower: Subject Indexing

KNOWLEDGE AREAS

Data Storage & Operations Data Engineering Data Warehousing & BI Data Modeling

TARGET AUDIENCE

Data Engineer Software Developer Data Architect Analytics Engineer Data Modeler BI Specialist

EXTERNAL SOURCES

• Wikipedia - Database index
• PostgreSQL - Comprehensive index types and usage
• MySQL - Index optimization guidelines
• Microsoft - SQL Server index design guide

TOP QUESTIONS

How do I decide which columns to index?

Index columns used in selective filters, joins, and sorts. Analyze query plans and cardinality to prioritize high-impact keys.

Category: Requirements

What is the downside of over-indexing?

Too many indexes slow down writes, inflate storage, and complicate maintenance. They can also mislead the optimizer.

Category: Risks

When is a full table scan preferable to indexing?

For low-selectivity queries touching most rows, scans can outperform index lookups and reduce random I/O.

Category: Decision

What is a covering index?

It contains all columns needed for a query, enabling index-only access and avoiding table lookups.

Category: Concepts

How do composite indexes help performance?

They optimize multi-column predicates and ordering. Column order matters for selectivity and prefix matching.

Category: Performance

What is cardinality and why does it matter?

Cardinality measures data distinctness; high-cardinality columns typically benefit more from B-tree indexes.

Category: Concepts

How should I maintain indexes over time?

Monitor bloat and fragmentation, rebuild or reindex as needed, and keep statistics fresh for the optimizer.

Category: Management

When are bitmap indexes appropriate?

They excel in read-heavy analytics with low-cardinality columns, common in data warehousing scenarios.

Category: Application

What’s a current topic in indexing technology?

Adaptive indexes and learned index structures explore ML-based approaches to indexing patterns.

Category: Topic