DataTerminology.comNEW
Also known as: Indices, Database Index
A database data structure that accelerates row retrieval by organizing column values for efficient lookup.
DESCRIPTION
Indexes improve query performance by providing alternative access paths to data. Common implementations include B-tree, hash, and GiST/GIN for specialized workloads. Well-designed indexes reduce I/O, but incur storage overhead and slow writes due to maintenance. Selectivity, column order, and covering properties determine effectiveness. Database optimizers choose among index scans, bitmap scans, and sequential scans based on statistics. Regular maintenance and monitoring ensure indexes remain beneficial as data evolves.
ALTERNATIVE DEFINITIONS
Wikipedia
A database index is a data structure that improves the speed of data retrieval operations on a database at the cost of additional writes and storage space.
Highlights the classic speed–storage trade-off.
Microsoft SQL Server Documentation
An index is an on-disk structure associated with a table or view that speeds the retrieval of rows from the table or view.
Frames indexes as physical structures tied to tables or views.
Microsoft SQL Server
An index is an on-disk structure associated with a table or view that speeds retrieval of rows.
Defines index as a physical structure associated with tables or views.
MySQL
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must scan the entire table to find the relevant rows.
Contrasts indexed lookup with full table scans.
RELATED TERMS
B-Tree Hash Index Primary Key Clustered Index Nonclustered Index Query Optimization Cardinality Covering Index Full-Text Index Sequential Scan Composite Index
COMPETING TERMS
Table Scan Materialized View
RELATIONS
- Narrower: B-Tree B-tree Index Clustered Index Covering Index Hash Index Inverted File Index Inverted Index Jaccard Index Search Index Spatial Index Vector Index
KNOWLEDGE AREAS
Data Storage & Operations Data Engineering Data Warehousing & BI Data Modeling Software Engineering
TARGET AUDIENCE
Data Engineer Software Developer Data Architect BI Specialist Data Modeler Analytics Engineer
EXTERNAL SOURCES
- Wikipedia - Database index
- PostgreSQL - PostgreSQL indexes guide
- MySQL - Index optimization in MySQL
- SQLite - Query planner and indexes
- PostgreSQL - Introduction to indexes in PostgreSQL
- Microsoft SQL Server - SQL Server index overview
- MySQL - MySQL documentation on indexes
TOP QUESTIONS
When should I create an Index on a column?
Index columns used in selective predicates, joins, or sorting. Avoid low-selectivity columns or those frequently updated unless necessary.
Category: Practice
What is the downside of too many Indexes?
They consume storage and slow inserts/updates/deletes due to maintenance. Over-indexing can also mislead the optimizer.
Category: Risks
How do B-tree and hash Indexes differ?
B-trees support range scans and ordering; hash indexes excel at equality lookups but typically don’t support range queries.
Category: Basics
What is a covering Index?
An index that contains all columns needed by a query, allowing retrieval without accessing the base table.
Category: Concept
How do I detect unused Indexes?
Use database usage statistics and monitoring views. Periodically review and drop redundant or unused indexes.
Category: Management
How does Index selectivity affect performance?
Higher selectivity (more distinct values) generally yields better filtering and performance benefits.
Category: Concept
What is a clustered Index?
It defines the physical order of data rows, often improving range queries but affecting insert patterns.
Category: Basics
How does an Index impact transaction throughput?
Each write updates indexes, increasing latency. Balance read vs write performance based on workload characteristics.
Category: Tradeoff
How often should Indexes be maintained?
Monitor fragmentation and statistics; rebuild or reorganize when performance degrades. Frequency depends on update patterns.
Category: Management