What Is an Index in SQL?

Introduction

An index is a database structure that helps the database locate rows without examining every row in a table. It stores selected key values in an organized form together with a way to reach the corresponding table data. The optimizer can use that structure for suitable filters, joins, sorting, grouping, uniqueness checks, and other operations.

An index resembles the index of a book: a reader searches a compact ordered list and follows a reference to the relevant page instead of reading the entire book. The analogy is useful, but database indexes also have to remain correct as rows are inserted, updated, and deleted. That maintenance consumes storage, CPU, I/O, logging, memory, and concurrency capacity.

Indexes are therefore workload decisions, not universal speed switches. A valuable index makes important reads sufficiently cheaper to justify its lifecycle cost. The exact structures and syntax differ among PostgreSQL, MySQL, SQL Server, Oracle, SQLite, and other products, so product-specific behavior is labeled throughout this tutorial.

Why Databases Need Indexes

As tables grow, scanning every row for each request becomes expensive. A login lookup should not inspect millions of accounts, and an order-history request should not read every customer's orders. Indexes provide smaller, searchable access paths that let the optimizer target the relevant subset.

They also support constraints and relationships. A unique index can enforce that an email or business key appears only once. An index on a foreign-key column can improve joins and parent-row changes in many workloads. The benefit depends on selectivity, query shape, data distribution, and database implementation.

A Real-World Analogy

Imagine a warehouse inventory ledger sorted only by the order in which entries arrived. To find one product, a worker may inspect every page. A separate catalog sorted by product code can point directly to matching ledger locations.

The catalog takes space and must be updated whenever inventory records change. Creating catalogs for product, supplier, color, date, price, and every possible combination would make updates burdensome. Database index design has the same tradeoff: choose access paths that serve real, frequent questions.

Sample Database

CREATE TABLE customers (
    customer_id   BIGINT PRIMARY KEY,
    email          VARCHAR(255) NOT NULL,
    customer_name VARCHAR(120) NOT NULL,
    region         VARCHAR(50) NOT NULL,
    status         VARCHAR(20) NOT NULL,
    created_at     TIMESTAMP NOT NULL
);

CREATE TABLE orders (
    order_id     BIGINT PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    order_date   DATE NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(14, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The examples use customer and order data to demonstrate identity lookups, range scans, joins, sorting, composite keys, uniqueness, and covering behavior.

What Happens Without an Index

SELECT customer_id, customer_name
FROM customers
WHERE email = 'alex@example.com';

Without a useful access path, the database may perform a table scan, reading rows and evaluating the predicate until all possible matches are considered. A scan is not inherently bad: it can be optimal for a small table or a query returning much of the data. It becomes costly when a large table is searched for a tiny subset repeatedly.

What Happens with an Index

CREATE UNIQUE INDEX ux_customers_email
ON customers (email);

The database creates an organized structure over email values. A matching query can navigate that structure and reach the row reference with far less work. The unique property also prevents duplicate non-null key values according to the product's null semantics.

The optimizer still chooses the plan. It may prefer a scan if most rows qualify, statistics indicate low benefit, or required output would cause too many random lookups.

How a B-Tree Index Works

B-tree and related balanced-tree structures are common defaults for relational indexes. Internal pages guide navigation through key ranges, while leaf pages contain ordered keys and row locators or data. The tree remains balanced so navigation requires relatively few page levels even as rows grow.

Ordered keys support equality searches, ranges, prefixes of composite keys, and compatible ordering. The exact leaf representation depends on whether the index is clustered, secondary, covering, or implemented differently by the database engine.

Basic CREATE INDEX Syntax

CREATE INDEX ix_orders_customer
ON orders (customer_id);

DROP INDEX ix_orders_customer;

CREATE INDEX is broadly recognized, but drop syntax, online build options, included columns, filters, storage settings, and concurrency behavior vary. Use migrations and validate the target database version before production changes.

Single-Column Index

A single-column index organizes one key, such as customer_id, order_date, or email. It is effective when important queries constrain that column selectively or use it as a join key.

Do not create one index per column automatically. Columns with few values, such as a binary flag, may not be selective enough alone, though they can still be useful in partial indexes or composite access paths for specific workloads.

Composite Index

CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

A composite index uses multiple key columns in a defined order. This example can support orders for one customer and a date range. It can often support a customer-only predicate, but generally not an order-date-only search as effectively because customer_id is the leading key.

Choose order from equality predicates, ranges, sorting, selectivity, and the set of query patterns. The simplistic rule "put the most selective column first" is not sufficient for every workload.

Unique Index

A unique index enforces key uniqueness while providing an access path. It is appropriate for real candidate keys such as a normalized email, external reference, or country-specific identifier when the business rule requires uniqueness.

Null handling differs across products, and case or collation rules can change what counts as equal. Normalize and validate the business meaning before relying on the index as a constraint.

Clustered Index Concept

A clustered index determines or closely controls how table rows are organized with its key in products such as SQL Server. Because a table's rows can follow only one primary physical organization at a time, such products allow one clustered index per table.

This is not portable behavior. InnoDB organizes table data around the primary key or an internal alternative, while PostgreSQL's CLUSTER rewrites a table once and does not continuously preserve ordering. Always use product-specific terminology accurately.

Nonclustered or Secondary Index

A nonclustered or secondary index stores its own key order and a locator for the base row. A query may use the index to find keys and then perform lookups to retrieve columns not stored in that index. Many lookups can become expensive when numerous rows qualify.

Products may include the clustered key or primary key in secondary entries automatically. This affects storage and key-design tradeoffs.

Clustered vs Nonclustered

CharacteristicClustered conceptNonclustered concept
Data organizationRows follow primary index organizationSeparate key structure
Count per tableNormally onePotentially several
Leaf levelContains table rows in SQL ServerContains keys and row locators
Best usePrimary range and row access patternAdditional search and ordering patterns
PortabilityHighly product-specificNames and internals vary

Hash Index

A hash index maps a key through a hash function and is designed primarily for equality lookup. It generally does not support ordered ranges or sorting like a B-tree. Availability and behavior vary significantly by database and storage engine.

Choose it only after confirming product support and measuring an equality-dominant workload. A default B-tree is often more versatile.

Full-Text Index

Full-text indexing tokenizes language content to support word, phrase, prefix, ranking, and linguistic searches. A normal B-tree cannot efficiently answer "contains this word anywhere in a long document" with a leading wildcard.

Full-text search has product-specific tokenizers, stop words, stemming, language settings, update behavior, and query syntax. It is a specialized search feature rather than a replacement for ordinary indexes.

Partial and Filtered Indexes

-- PostgreSQL and SQLite use partial-index terminology.
CREATE INDEX ix_orders_open_customer
ON orders (customer_id)
WHERE order_status = 'OPEN';

A partial or filtered index stores only rows satisfying a predicate. It can be smaller and more selective for stable queries such as open orders or unprocessed jobs. SQL Server calls the feature a filtered index; support and syntax differ elsewhere.

The query predicate must be compatible with the index predicate. Parameterization and optimizer inference can affect whether it is selected.

Function-Based and Expression Indexes

CREATE INDEX ix_customers_lower_email
ON customers (LOWER(email));

An expression index stores the result of a deterministic expression so a matching predicate can search it. This can support case-normalized email lookup or date extraction without scanning every row.

Syntax, determinism rules, generated-column alternatives, and function requirements differ. The query expression must match in a form the optimizer recognizes.

Covering Index

An index covers a query when it contains all columns needed for filtering, joining, ordering, and output, allowing the engine to avoid base-row lookups. Some databases use included non-key columns; others require all covered columns in the index definition or support index-only scans differently.

Covering can reduce reads, but wide indexes duplicate data, reduce cache density, and increase writes. Cover only high-value query patterns confirmed by plans.

Indexes and WHERE

Indexes are most helpful when a predicate identifies a small or otherwise useful range. Equality, bounded ranges, and left-anchored patterns often align with B-tree access. A query returning most rows may be faster with a scan.

Expressions, implicit type conversions, leading wildcards, and mismatched collations can prevent direct searches. Rewrite predicates to remain searchable where semantics allow.

Sargable Predicates

-- Often less searchable
WHERE EXTRACT(YEAR FROM order_date) = 2026

-- Usually more searchable
WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2027-01-01'

The second form exposes a range on the stored column. Product-specific expression indexes can support the first, but a clear range is portable and preserves date boundaries explicitly.

Indexes and JOIN

Indexes on join keys can reduce work when the optimizer begins with a selective set and looks up matching rows. Primary and unique keys are usually indexed, but foreign-key columns may require an explicit index depending on the product and workload.

A large join can still favor hash or merge processing over repeated lookups. Compatible data types, correct relationships, accurate statistics, and realistic cardinality estimates matter as much as index existence.

Indexes and ORDER BY

An index whose key order matches a query's filters and requested ordering can avoid a separate sort. Direction, null placement, collation, and mixed ascending or descending requirements affect compatibility by product.

Sorting a small result may be cheaper than maintaining a dedicated index. Inspect the plan and total workload cost.

Indexes and GROUP BY

Ordered input can help streaming aggregation, while other plans use hashing. An index aligned with grouping keys and predicates may reduce sorting or scanning, but it does not precompute the aggregate in a regular table index.

For repeated expensive summaries, compare ordinary indexing with materialized, indexed-view, or summary-table approaches and account for freshness or write maintenance.

Indexes and Primary Keys

A primary-key constraint commonly creates or requires a unique index, but the physical index type is product- and configuration-specific. The key enforces entity identity and supports references.

Choose keys that are stable, narrow enough for widespread use, and semantically correct. In engines where secondary indexes store the primary or clustered key, a wide key increases every secondary structure.

Indexes and Foreign Keys

Foreign-key constraints do not universally create child-column indexes automatically. Indexing the child key can improve joins and parent updates or deletes, particularly when the engine must verify related rows.

Not every foreign key needs a standalone index. A composite index beginning with the foreign key may already support the workload. Check metadata and plans before adding redundancy.

Selectivity and Cardinality

Selectivity describes how narrowly a predicate identifies rows. An email equality condition is usually highly selective, while a status with two values may match a large portion of the table. Optimizers use statistics to estimate these cardinalities.

Low-selectivity columns can still be useful in composite or filtered indexes. Data skew matters: one status may have very few rows while another has millions, so one cached plan may not suit both.

Statistics and the Optimizer

Statistics summarize key distributions and help the optimizer compare scans, seeks, joins, and memory needs. Stale or insufficient statistics can cause poor estimates even when a useful index exists.

Maintain statistics according to product guidance and data-change patterns. Compare estimated and actual rows at important plan operators to find skew and correlation problems.

Why an Index May Not Be Used

The optimizer may ignore an index because the query returns many rows, the table is small, the key order is unsuitable, statistics predict low benefit, an expression blocks searching, or base-row lookups would cost more than a scan. Parameter values and cache state also influence plan choice.

Do not force an index simply because it exists. Validate the alternative plan and fix the root cause when estimates or predicates are wrong.

Read Benefits

Well-chosen indexes can reduce pages read, CPU, elapsed time, sorting, and lock duration for suitable queries. They improve application responsiveness and allow larger datasets to serve point and range lookups predictably.

The benefit should be measured across representative parameters and concurrency, not inferred from index names or one warm-cache execution.

Storage Cost

Every index stores keys, locators, page metadata, and possibly included columns. Capacity estimates should also consider transaction logs, backups, replicas, rebuild workspace, and memory-cache competition.

Wide character keys and duplicate included data can make an index surprisingly large. Measure actual size after loading production-like values.

INSERT Cost

An insert must add entries to every affected index. Ordered keys can cause page splits or hot right-hand pages depending on key pattern and engine. More indexes increase logging and transaction duration.

Benchmark bulk loads and peak online writes before deploying several read-oriented indexes to a busy table.

UPDATE Cost

Updating an indexed key requires relocating or changing its entry. Updating an included column also modifies indexes that store it. An update to an unrelated column may avoid some index maintenance but still changes the base row.

Understand which columns applications actually update. A covering index over frequently changed fields may cost more than expected.

DELETE Cost

A delete removes the base row and corresponding entries from all indexes. Large purges can generate extensive logs, locking, and cleanup work. Database MVCC and vacuum behavior add product-specific lifecycle cost.

Test retention operations with the full index set and replicas, not on an empty development table.

Write Contention

Concurrent sessions can contend on popular index pages, especially monotonically increasing keys or a few repeated values. Symptoms include latch, lock, log, or I/O waits depending on the engine.

Diagnosis requires wait and plan evidence. Randomizing keys can reduce one hotspot while harming locality and increasing fragmentation, so avoid generic fixes.

Redundant and Overlapping Indexes

Two indexes with the same leading keys may serve overlapping workloads. The wider one might cover additional queries, but both consume write and storage resources. Similar names do not prove redundancy; uniqueness, filters, ordering, included columns, and operational use all matter.

Review usage and definitions before removal. An index unused during one observation window may support monthly reporting, constraints, or failover workloads.

Index Key Width and Data Types

Narrow keys allow more entries per page, which can reduce tree depth, memory use, I/O, and secondary-index overhead. Wide strings, large composite keys, and unnecessary precision consume more space and make comparison work heavier. In engines where secondary indexes carry the primary or clustered key, that choice is multiplied across many structures.

Choose data types from domain requirements first, then avoid accidental width. Do not replace a meaningful stable key merely to save bytes without considering joins, migration, and business identity. Conversely, do not use long descriptive text as an index key when a compact identifier provides the same relationship.

Collation also affects string comparison, uniqueness, order, and key size. Test real multilingual values rather than only short ASCII samples.

Key Columns vs Included Columns

Key columns determine index ordering and navigation. Included columns, where supported, are stored at the leaf level to cover output without changing the searchable key sequence. This distinction can make a covering index smaller and avoid imposing an unnecessary sort order on output-only data.

Included values still consume storage and must be maintained when updated. They are not free payload. A query selecting dozens of columns should not automatically receive a matching giant index; fetching a modest number of base rows may be cheaper overall.

Support varies by product. PostgreSQL and SQL Server provide inclusion mechanisms with different index-only and visibility behavior, while other engines represent coverage differently. Validate actual plans and storage.

Partitioned Tables and Local Indexes

Partitioned tables introduce choices such as local or partition-aligned indexes, global indexes, and per-partition maintenance depending on the database. A query benefits when its predicates enable partition pruning and the remaining partitions have useful access paths.

Partitioning is not a substitute for indexing, and indexing is not a substitute for pruning. A function or cast on the partition key can force access to many partitions even when each partition has an index. Verify selected partitions in the execution plan.

Consider retention operations, partition switching or exchange, uniqueness rules, rebuild scope, and recovery. Product support differs significantly, so design against the deployed engine rather than generic terminology.

Checking Whether an Index Is Used

-- PostgreSQL example; other products use different tools.
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 101
  AND order_date >= DATE '2026-01-01';

Inspect the actual plan for index seeks or scans, key and residual predicates, rows read and returned, estimates, base-row lookups, sorts, spills, CPU, and reads. ANALYZE executes the statement in PostgreSQL, so use caution with modifying queries.

Benchmarking an Index

Capture a baseline before creation using production-like data, parameters, cache conditions, and concurrency. Measure latency percentiles, CPU, logical and physical reads, rows, waits, memory, log bytes, and write throughput.

After creation, repeat the same workload and include index-build and maintenance effects. One faster SELECT does not justify an index that significantly harms the system's dominant writes.

Index Maintenance

Indexes can accumulate fragmentation, dead entries, uneven page density, or stale statistics depending on database behavior. Rebuild, reorganize, vacuum, analyze, and online-maintenance capabilities are product-specific.

Use measured thresholds and workload impact rather than rebuilding every index on a fixed schedule. Maintenance itself consumes CPU, I/O, logging, temporary storage, and sometimes locks.

Online Index Creation

Some products and editions offer concurrent or online index builds that reduce blocking, but they still consume resources and may take longer. Availability, syntax, restrictions, and failure behavior vary.

Plan creation windows, disk and log capacity, replica impact, cancellation, and rollback. Test the exact operation at realistic scale.

Monitoring Index Value

Track usage alongside cost. Useful evidence includes seek and scan counts, rows read, query latency, plan history, index size, write frequency, page density, fragmentation, statistics age, lock or latch waits, and maintenance duration. Database counters can reset during restart or failover, so retain history and interpret observation windows carefully.

One usage count does not prove value. An index used once for a critical month-end close may matter more than one used frequently by an inefficient low-value query. Connect each important index to supported workloads and an accountable owner.

Watch for plan regressions after data growth, statistics updates, schema changes, database upgrades, and new competing indexes. An added index can cause the optimizer to choose a new plan that helps one parameter and harms another.

Safely Retiring an Index

Before removal, confirm the index does not enforce a primary key, unique constraint, or required physical design. Review plans, scheduled workloads, reporting cycles, replicas, and failover environments. Compare overlapping indexes by complete definition rather than name.

Where the product supports disabling or invisible indexes, a controlled trial may reveal plan effects without immediately discarding the definition, but these features have their own write and maintenance semantics. Otherwise, script recreation and remove the index during an observed release window.

Monitor latency, reads, CPU, waits, and write behavior after retirement. Roll back if important workloads regress. Record why the index was removed so future teams do not recreate the same cost without new evidence.

E-Commerce Product Search

A catalog may filter publication status, category, brand, price range, and sort order. One huge composite index cannot efficiently serve every combination. Start with dominant paths, use full-text or search systems for language search, and retain filters that reduce candidates early.

Measure pagination and inventory joins as part of the complete request, not only the product lookup.

Banking Transaction History

A common access pattern retrieves recent transactions for one account ordered by posting time. A composite index beginning with account ID and date can support filtering and ordering, with a unique transaction key completing deterministic order.

Test hot accounts, retention, reversals, and deep history. Preserve authorization and exact ordering while tuning.

Employee Search

An HR application may search employee ID, normalized email, department, or active status. Unique identifiers deserve dedicated keys, while department and status may work as a composite path for manager lists.

Names are not stable unique keys. Language-aware name search may need full-text or specialized search rather than a basic B-tree.

Software Testing Dashboard

A dashboard often filters executions by build, suite, environment, status, and start time. Composite indexes should follow common drill-down paths and time ranges. Very wide covering indexes can be costly because execution events are written frequently.

Partitioning or asynchronous summaries may complement indexes when raw history becomes large.

Login Authentication

Authentication commonly performs a unique lookup on normalized username or email. A unique index enforces identity and avoids scanning account records. Password verification remains an application cryptographic operation; the index should never contain plaintext secrets.

Rate limiting, constant-time comparison, account-state checks, and audit controls remain necessary. A fast lookup is only one part of secure authentication.

When to Create an Index

  • An important query repeatedly scans a large table for few rows.
  • A selective filter or join key lacks a suitable leading access path.
  • Sorting or grouping dominates a frequent workload.
  • A real business key requires uniqueness enforcement.
  • A high-value query can be covered without excessive width.
  • Measured read benefits exceed storage and write costs.

When to Avoid an Index

  • The table is small and scans are already inexpensive.
  • The proposed key has little usefulness for real predicates.
  • A nearly identical index already supports the workload.
  • The table is extremely write-heavy and read benefit is minor.
  • The query returns most rows, making a scan appropriate.
  • No baseline, plan, or measurable requirement supports creation.

Common Index Mistakes

  • Creating one index for every column.
  • Ignoring composite key order and leading columns.
  • Adding wide covering indexes without write testing.
  • Assuming an index is used because its name matches a query.
  • Forcing indexes instead of correcting statistics or predicates.
  • Using functions and implicit conversions on search columns.
  • Keeping redundant indexes indefinitely.
  • Ignoring foreign-key access and parent-change behavior.
  • Rebuilding every index without evidence.
  • Testing only one parameter and a tiny dataset.
  • Overlooking log, backup, replica, and cache costs.
  • Treating clustered behavior as portable across databases.

Index Best Practices

  1. Start from an important measured query and its actual plan.
  2. Define equality, range, join, order, and output requirements.
  3. Choose the narrowest key that serves meaningful workloads.
  4. Keep predicates sargable and data types compatible.
  5. Use unique indexes only for genuine uniqueness rules.
  6. Test common, rare, empty, and worst-case parameters.
  7. Benchmark read and write behavior under concurrency.
  8. Monitor usage, size, statistics, waits, and regressions.
  9. Review overlaps before adding or removing indexes.
  10. Document purpose, owner, supported queries, and rollback.

Interview Questions and Answers

What is an SQL index?

It is an auxiliary database structure that organizes selected key values and provides a path to matching table rows.

Why can indexes speed reads?

They can let the optimizer navigate to a selective key range instead of scanning every table row.

Why do indexes slow writes?

Every affected insert, update, and delete must also maintain index entries and generate associated storage and log work.

What is a composite index?

It is an index with multiple ordered key columns. Leading-key order determines which query patterns it can support efficiently.

What is a covering index?

It contains every column a query needs, allowing the engine to answer without fetching additional base-row data.

Why might an optimizer choose a scan?

A scan can be cheaper when many rows qualify, the table is small, lookups are expensive, statistics predict low selectivity, or predicates do not match the index.

Index Design Checklist

AreaReview question
WorkloadWhich measured queries and parameters need improvement?
KeysWhich equality, range, join, and ordering columns matter?
SelectivityHow many rows qualify across common and skewed values?
CoverageAre lookups costly enough to justify included data?
WritesWhat happens to inserts, updates, deletes, and loads?
CapacityAre storage, logging, backup, and rebuild costs acceptable?
OverlapDoes another index already provide the leading access path?
EvidenceDo actual plans and benchmarks demonstrate net benefit?

Quick Reference

-- Equality lookup and uniqueness
CREATE UNIQUE INDEX ux_customers_email
ON customers (email);

-- Join, range, and ordered customer history
CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

-- Remove an obsolete index using your product's syntax
DROP INDEX ix_orders_customer_date;

Key Takeaway

An SQL index is an organized access path that can make selective filters, joins, ranges, ordering, and uniqueness checks dramatically cheaper. The optimizer decides whether to use it based on query shape, statistics, expected rows, and total plan cost.

Every index has a lifecycle price: storage, cache, logging, write maintenance, backup, replication, rebuild, monitoring, and ownership. Design from measured workloads, choose keys deliberately, validate actual plans, test concurrency and writes, and remove structures that no longer provide net value. Good indexing is not about creating the most indexes; it is about maintaining the smallest effective set of access paths for the system's real work.