Composite Index in SQL

Introduction

A composite index is an index whose ordered key contains two or more columns. It is also called a multicolumn or compound index. Instead of maintaining independent structures for each column, the database orders complete key tuples such as (customer_id, order_date, order_id). That organization can support multi-column filters, joins, ranges, sorting, grouping, uniqueness, and query coverage.

The important idea is not simply that several columns appear in one definition. Their sequence determines how values are grouped and which predicates can navigate the structure efficiently. An index on (department_id, salary) is not equivalent to one on (salary, department_id), even though both mention the same columns.

This tutorial uses broadly recognizable SQL and labels SQL Server-specific features such as included columns and filtered indexes. Optimizers in PostgreSQL, MySQL, Oracle, SQLite, and SQL Server have different skip-scan, index-combination, ordering, and visibility capabilities, so actual plans remain the final evidence.

What Is a Composite Index?

A composite index stores multiple column values as one ordered search key. For an index on (department_id, salary), entries are sorted first by department. Within each department group, they are sorted by salary. A query that selects one department and a salary range can therefore navigate directly to a compact portion of the index.

The index may be clustered or nonclustered, unique or nonunique, complete or filtered, depending on database support and design. The phrase composite describes the number of key columns, not the physical index type.

Why Composite Indexes Are Needed

Real queries often identify rows through combinations. An order-history request specifies a customer and date range. Inventory lookup uses warehouse and product. A test dashboard uses project, environment, status, and execution time. A composite key can reflect these combinations more directly than separate one-column indexes.

Some optimizers combine multiple indexes through bitmap, intersection, or merge techniques, but combination has its own work and may not preserve useful ordering. A well-designed composite index can provide one ordered path and sometimes cover all required output.

A Real-World Analogy

Imagine student records filed first by class and then by roll number. Finding roll number 102 in class 10 is straightforward: locate class 10, then navigate within that smaller group. Searching roll number 102 across every class is less direct because the roll number sequence restarts inside each class.

This resembles an index on (class_id, roll_number). The leading class determines the major grouping, while roll number refines position within it.

Sample Database

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    salary        DECIMAL(12, 2) NOT NULL,
    city          VARCHAR(80) NOT NULL,
    hired_at      DATE 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
);

These tables support examples involving equality, ranges, ordering, uniqueness, joins, and reporting. Administrative syntax can vary by database engine.

Composite Index Syntax

CREATE INDEX ix_employees_department_salary
ON employees (department_id, salary);

CREATE INDEX ix_orders_customer_date_id
ON orders (customer_id, order_date, order_id);

Columns inside the parentheses are key columns in left-to-right order. A database imposes limits on key count, total key size, data types, and index count, so check the target product before designing very wide definitions.

How Composite Ordering Works

A B-tree compares the first key. When first-key values tie, it compares the second, then the third, continuing until it can distinguish entries. This is lexicographic tuple order. With (department_id, salary), all department 10 rows form one region, internally ordered by salary, followed by department 20 and its salary sequence.

That structure explains both the strength and limitation of composite indexes. Once the leading key is known, later keys narrow a compact range. Without the leading key, matching later values can be scattered across many leading-key groups.

The Leftmost Prefix Principle

For a conventional B-tree on (department_id, city, salary), the natural leading prefixes are (department_id), (department_id, city), and the complete three-column tuple. Queries beginning with those predicates can often seek efficiently.

A predicate only on city or salary lacks the first key. Some products and plans can use skip scans, index scans, or index combination, but this is database- and data-dependent. Do not promise efficient nonleading access from the leftmost-prefix rule alone.

Why Column Order Matters

-- Optimized for department-led access
CREATE INDEX ix_department_salary
ON employees (department_id, salary);

-- Optimized for salary-led access
CREATE INDEX ix_salary_department
ON employees (salary, department_id);

The first index groups salaries within each department; the second groups departments within each salary. Choose the order from actual equality filters, ranges, joins, sorting, grouping, selectivity, and query frequency. Identical column sets do not make definitions interchangeable.

Equality Predicates

SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = 10
  AND salary = 75000;

Equality on both keys can identify a narrow tuple range. Predicate order in the SQL text normally does not have to match index order; the optimizer reasons from expressions. Key order still controls the physical search path and whether other query patterns can use prefixes.

Range Predicates

SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = 10
  AND salary >= 65000
  AND salary < 80000;

Equality fixes the department group, then the salary range identifies a continuous interval within it. This equality-then-range shape is one of the most effective composite-index patterns.

Equality Before Range Guideline

Columns constrained by equality are commonly placed before the first range column. Once navigation enters a range on one key, keys to its right may still help filtering or coverage but often cannot narrow the initial seek in the same way.

This is a guideline, not a mechanical law. Ordering requirements, uniqueness, skip scans, data distribution, and several competing query patterns can justify another sequence. Validate plans rather than designing from slogans.

Three-Column Composite Index

CREATE INDEX ix_orders_customer_status_date
ON orders (customer_id, order_status, order_date);

This key can support one customer's orders, one customer's orders in a status, and a date range within one customer and status. It does not automatically provide an efficient global date search because date is the third key.

Adding columns broadens potential use but also widens every entry, reduces page density, and increases maintenance. Each column needs a demonstrated role.

Composite vs Single-Column Indexes

CharacteristicComposite indexSeparate single-column indexes
Multi-column filterOne direct ordered pathOptimizer may combine or choose one
OrderingCan preserve tuple orderUsually cannot combine into one order
Leading prefixCan serve prefix queriesEach column has its own entry point
WidthWider entriesSeveral narrower structures
Write costOne structure to maintainMultiple structures to maintain
Best choiceStable combination patternsIndependent column searches

Neither approach is always superior. Compare query plans, storage, writes, and the number of supported workloads.

Composite Index and WHERE

A composite index is useful when predicates align with a leading tuple. Equality, IN, bounded ranges, and compatible prefix patterns are common candidates. Functions, implicit conversions, and mismatched data types can prevent direct navigation even when the right columns are present.

Keep predicates sargable and compare actual seek predicates with residual predicates in the plan. A column may be listed in the index yet used only after entries are read.

Skipping a Middle Column

-- Index: (department_id, city, salary)
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10
  AND salary > 70000;

The leading department can define a seek range, but salary values remain distributed among city groups because the middle key is unconstrained. The engine may scan the department range and apply salary as a residual condition. Whether this is acceptable depends on group size and cost.

IN Predicates

An IN list on a leading key can behave like several equality probes. With (department_id, salary), the optimizer may seek each requested department and then evaluate the salary interval. Very large lists can change cost and plan shape.

Parameterization and cardinality estimation influence the choice. Test realistic list sizes instead of assuming one seek operator always means little work.

Composite Index and ORDER BY

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 201
ORDER BY order_date, order_id;

An index on (customer_id, order_date, order_id) can fix customer through equality and return the remaining keys in requested order, avoiding a separate sort. Without the customer predicate, the index is primarily ordered by customer, not globally by date.

Always keep ORDER BY when result sequence matters. Index order is a plan optimization, not a guarantee in its absence.

ASC and DESC Directions

CREATE INDEX ix_orders_customer_date_desc
ON orders (customer_id ASC, order_date DESC, order_id DESC);

This design can align with newest-first customer history. Many databases can scan an entire index backward, making all-directions reversal possible, while mixed-direction requirements often need a matching definition. Product behavior, null placement, and collation must be verified.

Composite Index and JOIN

Composite foreign keys and multi-tenant relationships often require joins on more than one column. An index on (tenant_id, customer_id) can support a join that preserves tenant isolation and locates one customer. The referenced key normally needs a matching primary or unique constraint.

Compatible data types, nullability, collation, statistics, and input cardinality remain important. A large join may use hash or merge processing even when a composite index exists.

Composite Index and GROUP BY

SELECT customer_id, order_status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, order_status;

An index beginning with (customer_id, order_status) can supply ordered input for streaming aggregation and can be narrower than the base table. The optimizer may still prefer hash aggregation if it estimates that approach cheaper.

Composite Index and DISTINCT

SELECT DISTINCT customer_id, order_status can benefit from matching ordered keys because duplicate tuples are adjacent. This can reduce sorting or hashing. If the query includes non-key expressions or a different tuple order, additional work may remain.

Do not create a permanent index solely to optimize a rare small distinct query without measuring its total value.

Composite Index and LIKE

A left-anchored pattern on a suitable key can act as a range. An index on (department_id, employee_name) may support one department plus employee_name LIKE 'Ali%'. A leading wildcard such as '%lice%' normally cannot navigate an ordinary B-tree from the left.

Collation and normalization affect matching. Full-text or specialized search is often better for contains-style language queries.

Composite Unique Index

CREATE UNIQUE INDEX ux_enrollment_student_course
ON enrollment (student_id, course_id);

This constraint prevents the same student-course combination from appearing twice while permitting each student and course to appear in other combinations. Composite uniqueness models a tuple-level business rule, not individual-column uniqueness.

Null treatment differs across database products. Confirm whether nullable combinations match the intended business rule.

Composite Primary Key

CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    line_no    INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, line_no)
);

The pair identifies one line within an order. Composite primary keys can express natural relationships clearly, but their width propagates into foreign keys and, in some engines, secondary index locators. A surrogate key may simplify references while a unique composite constraint preserves the business rule.

Composite Clustered Index

In SQL Server, a clustered index can use several key columns, and its leaf level contains complete table rows. A key such as (account_id, transaction_time, transaction_id) can group one account's chronological ledger. Every nonclustered locator then carries that composite clustered key.

Clustered composite keys should be narrow, stable, unique where practical, and aligned with dominant access patterns. Their impact extends across the entire table.

Composite Nonclustered Index

A composite nonclustered index is a separate secondary structure. Its leaf entries contain the composite keys, a row locator, and any included columns. Most examples in this tutorial use this common form.

When the base table is clustered, SQL Server uses the clustered key as the locator. When it is a heap, the locator is a row identifier. Other databases have their own secondary-index locator models.

Composite Covering Index

-- SQL Server example
CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date)
INCLUDE (order_status, total_amount);

The key supports customer and date navigation; included values cover output without expanding the ordered key. This distinction keeps navigation narrower than placing every output column in the key.

Included columns still consume leaf space and write resources. Coverage is query-specific and should be reserved for valuable, frequent paths.

Composite Filtered or Partial Index

-- SQL Server filtered-index syntax
CREATE INDEX ix_orders_open_customer_date
ON orders (customer_id, order_date)
INCLUDE (total_amount)
WHERE order_status = 'OPEN';

This smaller index serves open-order queries without indexing completed rows. PostgreSQL and SQLite use partial-index terminology for related functionality. Predicate matching, parameterization, determinism, and supported expressions vary.

Composite Index and NULL

Composite indexes can contain null key components in most relational databases, but ordering and uniqueness semantics differ. Predicates using IS NULL can often use a suitable index. A filtered or partial index may target null or non-null subsets.

Do not substitute equality with null. SQL three-valued logic and product-specific unique-null rules must be reflected in tests.

Selectivity and Data Distribution

Selectivity describes how much a predicate narrows the data. A low-cardinality leading key can still be effective when equality isolates a useful group and later keys provide a selective range. For example, status may have few values, but a small pending status combined with created time can support a queue.

Skew changes estimates. One tenant may own half the rows while most own hundreds. Test common and extreme values, and keep statistics representative.

Most Selective First Is Not a Universal Rule

Choosing the most selective column first ignores query shape. If important requests always specify customer equality and then a date range, customer belongs first even if dates have more distinct values globally. Leading order must serve usable prefixes, equality groups, ranges, and sorting.

Selectivity matters within that context, but it does not replace workload analysis.

Query Optimizer Decisions

The optimizer compares available seeks, scans, index combinations, lookups, joins, sorts, and aggregations. It can ignore a matching composite index if many rows qualify, the table is small, lookup cost is high, statistics predict little benefit, or another index covers the query more cheaply.

An index definition describes an option, not a command to the optimizer. Avoid forcing it until statistics, predicates, parameter sensitivity, and alternatives have been investigated.

Skip Scans and Nonleading Columns

Some database engines can perform skip-scan strategies that repeatedly probe later keys across distinct leading-key values. PostgreSQL and MySQL capabilities depend on version, data distribution, and plan cost; Oracle also has an index skip scan feature. SQL Server may scan or use other strategies rather than providing an equivalent guarantee.

Treat nonleading-key access as a measured optimizer capability, not a portable property of every composite B-tree.

Index Intersection

An optimizer may combine separate indexes for predicates on different columns. This can avoid a new composite index for occasional combinations, but intersecting row identifiers has CPU and memory cost and may not provide requested order or coverage.

A frequent stable combination often benefits from one targeted composite structure. Independent searches may justify separate indexes. Compare complete workload cost.

Reading the Execution Plan

Inspect actual plans for index seeks or scans, seek predicates, residual predicates, rows read and returned, estimates, base-row lookups, sorts, spills, memory grants, and join choices. A seek can still read many rows when the range is broad, while a narrow index scan can be efficient.

Plans should be tested with representative parameters because one composite order may perform differently for skewed values.

Checking Composite Index Usage

-- SQL Server usage counters
SELECT
    i.name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.database_id = DB_ID()
 AND s.object_id = i.object_id
 AND s.index_id = i.index_id
WHERE i.object_id = OBJECT_ID('dbo.orders');

Counters reset during restart and other lifecycle events. They do not show which key prefixes were useful and can miss infrequent critical workloads. Combine them with plan history, query statistics, index size, and owner knowledge.

Benchmarking a Composite Index

Capture a baseline with production-like row counts, distributions, parameters, output columns, cache conditions, and concurrency. Measure latency percentiles, CPU, logical and physical reads, rows, lookups, sorts, memory, waits, log bytes, and write throughput.

After creation, repeat the same workload. One faster SELECT does not justify an index that harms dominant inserts or duplicates an existing access path.

INSERT Performance

Every insert must create a composite key entry in each affected index. Wide keys reduce page density, increase comparison work, and can split pages when insertion locations are scattered. Sequential leading keys preserve locality but may concentrate concurrent writes.

Test bulk loading and normal concurrency with all production indexes, logging, replicas, and constraints enabled.

UPDATE Performance

Changing any composite key component can relocate the entry because tuple order changes. Updating an included column changes leaf payload without changing navigation, but still generates work. A mutable leading key can cause broad maintenance and unstable access patterns.

Choose stable keys where possible and benchmark real update batches, not only reads.

DELETE Performance

Deleting a row removes entries from every related index. Retention purges can generate significant logging, locks, ghost cleanup, and replica traffic. Numerous wide composite indexes multiply the cost.

Batching or partition maintenance may help at scale, provided referential and recovery requirements are preserved.

Storage Considerations

Storage includes every key value, row locator, included value, internal page, and unused page space. Wide strings, excessive precision, large composites, and duplicated overlapping definitions reduce cache density and increase backups and maintenance.

In SQL Server, clustered-key columns also appear in nonclustered locators. In InnoDB, primary-key columns appear in secondary entries. Account for those implicit values when estimating size.

Overlapping Composite Indexes

Indexes on (customer_id), (customer_id, order_date), and (customer_id, order_date, order_status) overlap, but they are not automatically interchangeable. The narrow index may be cheaper to scan; the wider ones may support ranges or coverage. Included columns, directions, filters, and uniqueness also matter.

Consolidate only after comparing complete definitions and actual workloads. One giant index is not always a sound replacement.

Employee Management Use Case

An HR report frequently selects employees in one department above a salary threshold. (department_id, salary) aligns with equality followed by range. Name and city can be included for a small report if lookup cost is demonstrated.

A global salary report needs another path because department is not fixed. One index cannot optimally serve every query.

E-Commerce Order History

A customer-history query filters customer ID and date, sorts newest first, and displays status and total. A composite key on (customer_id, order_date DESC, order_id DESC) with output coverage can serve that workflow and provide deterministic pagination.

Global date processing or status queues may need separate targeted structures or reporting storage.

Banking Transactions

An account statement uses (account_id, posting_time, transaction_id) for one account's ordered range. The tiebreaker makes pagination deterministic. A unique reference code deserves a separate unique index for one-transaction lookup.

Test hot accounts, concurrent posting, reversals, retention, and authorization predicates rather than only isolated reads.

Software Testing Dashboard

A dashboard may use (project_id, environment, started_at) for drill-downs and another filtered index for failures. Test-result tables are write-heavy, so covering every chart with a wide index can damage ingestion and cleanup. Precomputed summaries may be better for long-range trends.

Inventory Management

Current stock often uses a unique tuple such as (warehouse_id, product_id). Movement history may use (warehouse_id, product_id, occurred_at). Separating current balance from append-only events can keep each key aligned with its workload.

Login Audit Reporting

Audit queries commonly filter account and time or status and time. An index on (account_id, attempted_at) supports one account's history, while a filtered failure index can support security review. Retention, privacy, and high write volume limit how much payload should be included.

Cross-Database Considerations

AreaWhy behavior differs
Leading prefixesSkip-scan and index-combination capabilities vary
CoverageIncluded columns and index-only visibility differ
DirectionsBackward scans, null order, and mixed directions vary
Partial indexesSyntax and predicate matching differ
Null uniquenessMultiple-null treatment is product-specific
LocatorsClustered keys, primary keys, row IDs, or tuple IDs may be used

Common Mistakes

  • Choosing column order from table order rather than query patterns.
  • Applying "most selective first" without considering equality and range.
  • Assuming every listed column narrows the seek.
  • Expecting a later key to behave like an independent leading index.
  • Adding too many key columns instead of using restrained coverage.
  • Ignoring requested sort direction and deterministic tiebreakers.
  • Creating overlapping indexes without reviewing complete definitions.
  • Forcing one index to serve unrelated workloads.
  • Testing only one parameter value on a tiny dataset.
  • Ignoring write, storage, log, backup, and maintenance costs.
  • Relying on apparent output order without ORDER BY.
  • Assuming skip-scan behavior is portable across databases.

Best Practices

  1. Start from important measured queries and actual plans.
  2. Map equality, range, join, order, group, and output columns.
  3. Choose leading prefixes that support several valuable paths.
  4. Place range columns deliberately after useful equality groups.
  5. Keep keys narrow, stable, and no wider than required.
  6. Use included columns only for proven coverage benefits.
  7. Add deterministic tiebreakers for ordered pagination.
  8. Test skewed, common, rare, empty, and worst-case parameters.
  9. Benchmark reads and writes under realistic concurrency.
  10. Review overlap, usage, size, and owner purpose regularly.

Interview Questions and Answers

How would you define a composite index?

It is an index whose ordered key contains two or more columns, compared from left to right as a tuple.

Why does column order matter?

The leading columns define the major key groups and determine which prefixes, ranges, and orderings can be navigated directly.

What is the leftmost prefix principle?

A composite B-tree naturally supports searches beginning with its leading key sequence, while nonleading-only access depends on scans or product-specific optimizer strategies.

Why are equality columns often placed before range columns?

Equality fixes a key group, allowing the following range to identify a continuous interval inside it.

Can a composite index replace all single-column indexes?

No. Queries searching later keys independently may still require separate indexes, and a narrower structure may be cheaper for some workloads.

What is a composite covering index?

It combines ordered key columns with enough stored output values to answer a specific query without a base-row lookup.

Can a composite index enforce uniqueness?

Yes. A unique composite index prevents duplicate tuples while allowing individual components to repeat.

Does an index guarantee output order?

No. Only an explicit ORDER BY guarantees result sequence.

Practical Exercises

  1. Create (department_id, salary) and test leading and nonleading predicates.
  2. Reverse the key order and compare actual plans and logical reads.
  3. Skip a middle key in a three-column index and identify residual filtering.
  4. Compare separate one-column indexes with one composite index.
  5. Add an included output column and measure lookup reduction.
  6. Create a unique two-column business rule and test duplicates and nulls.
  7. Test matching and mismatched ORDER BY directions.
  8. Measure insert and update throughput before retaining the design.

Design Checklist

AreaReview question
PredicatesWhich columns use equality, ranges, joins, and patterns?
PrefixesWhich valuable queries share leading columns?
OrderingCan the key avoid important sorts and provide a tiebreaker?
CoverageAre base-row lookups costly enough to include output?
DistributionHow selective and skewed are common values?
OverlapWhich existing indexes duplicate key prefixes?
WritesWhat is the impact on inserts, updates, deletes, and loads?
EvidenceDo actual plans and benchmarks demonstrate net value?

Quick Reference

-- Equality followed by range
CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

-- Composite uniqueness
CREATE UNIQUE INDEX ux_enrollment_student_course
ON enrollment (student_id, course_id);

-- SQL Server covering index
CREATE INDEX ix_orders_customer_date_cover
ON orders (customer_id, order_date)
INCLUDE (order_status, total_amount);

-- Remove an obsolete index using product syntax
DROP INDEX ix_orders_customer_date ON orders;

Key Takeaway

A composite index is an ordered tuple, not a bag of columns. The leading sequence determines which equality groups, ranges, joins, sorts, and grouping operations can use the structure directly. The most reliable designs start with real query shapes, place equality and range columns deliberately, and keep keys narrow enough to preserve density and write performance.

Use actual plans to distinguish seek predicates from residual filters, and never assume nonleading access or skip scans behave identically across database engines. Compare composite and single-column alternatives, add coverage selectively, test skew and concurrency, and remove overlap that no longer provides net value. A good composite index serves several important access patterns without becoming a wide, expensive attempt to support every possible query.