Indexing Strategies in SQL

Introduction

An effective SQL indexing strategy is a repeatable way to select, design, validate, and maintain indexes for a real workload. It is not a checklist that recommends an index for every column. Each index consumes storage, must be updated by data changes, influences optimizer choices, and creates an operational object that someone must understand and maintain.

The objective is therefore the minimum effective set of indexes that keeps important queries within their service targets without imposing unacceptable write cost. Reaching that balance requires query evidence, representative data, execution plans, measurements, and periodic review as the application evolves.

What Is an Indexing Strategy?

An indexing strategy connects business operations to physical access paths. It identifies the queries that matter, studies their filters, joins, ordering, grouping, and returned columns, and then proposes indexes whose key order matches those operations. It also records why each index exists and how its benefit will be measured.

Consider a frequent employee search that limits one department and scans a salary range. A possible composite index places the equality column first and the range column second:

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

CREATE INDEX ix_employees_department_salary
ON employees (department_id, salary);

This is a candidate, not a guaranteed answer. Table size, value distribution, selected columns, cache state, concurrent writes, and the optimizer's cost model determine whether the index is actually useful.

Why Indexing Strategies Matter

Without deliberate design, teams often alternate between two extremes: too few indexes and too many. Too few can cause repeated scans, large sorts, excessive I/O, and slow joins. Too many can reduce insert throughput, lengthen updates, increase logging and backup size, consume memory, and confuse both maintainers and the optimizer with overlapping choices.

A strategy turns indexing into an engineering process. It prioritizes user-visible and resource-intensive queries, defines acceptable read and write tradeoffs, and verifies results with plans and runtime metrics rather than assuming that every seek is good or every scan is bad.

Understand the Workload First

Index design begins with workload classification. A product catalog may be read-heavy, an event collector write-heavy, an order system mixed, and a warehouse analytical. The same index set cannot serve all four efficiently because their access patterns and performance goals differ.

WorkloadTypical activityIndexing emphasis
Read-heavySelective lookups, browsing, dashboardsComposite and covering indexes for common paths
Write-heavyFrequent inserts, updates, deletesFew narrow indexes with measured write cost
Mixed OLTPShort transactions and operational readsBalance latency, locking, storage, and write throughput
AnalyticalLarge scans, joins, and aggregationsColumnstore, partitioning, or specialized structures

Collect normalized query statistics, execution frequency, elapsed time, CPU, logical reads, row counts, and concurrency. A moderately slow query executed thousands of times may deserve attention before a rare report with a longer individual duration.

Sample Database

The following compact table supports the examples. Small data is suitable for understanding syntax, but performance conclusions require production-like volume, skew, and concurrent activity.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10,2),
    city VARCHAR(100),
    joining_date DATE,
    employment_status VARCHAR(20)
);

INSERT INTO employees VALUES
(101, 'Alice', 10, 60000, 'Chicago', '2022-01-15', 'ACTIVE'),
(102, 'Bob', 20, 55000, 'Dallas', '2021-03-10', 'ACTIVE'),
(103, 'Carol', 10, 75000, 'Chicago', '2020-06-20', 'ACTIVE'),
(104, 'David', 30, 65000, 'New York', '2019-09-05', 'INACTIVE'),
(105, 'Emma', 20, 58000, 'Dallas', '2023-02-12', 'ACTIVE'),
(106, 'Frank', 10, 72000, 'Boston', '2021-11-18', 'ACTIVE');

Strategy 1: Index Frequently Queried Columns

Columns repeatedly used in selective WHERE predicates are natural candidates. Customer identifiers, product SKUs, email addresses, external references, and transaction numbers often fit this pattern. A city filter might suggest CREATE INDEX ix_employees_city ON employees (city).

Frequency alone is insufficient. If the table is tiny, most rows match, or an existing composite index already has the column as a useful leading key, another index may add cost without improving the plan.

Strategy 2: Evaluate Selectivity and Distribution

Selectivity describes how strongly a predicate narrows the table. A unique employee ID is highly selective. A status containing only ACTIVE and INACTIVE is low-cardinality, and an ACTIVE predicate matching 95 percent of rows may be cheaper to satisfy with a scan.

Cardinality alone is not the full story. A low-cardinality value can still be useful when one value is rare, when it leads a partial or filtered index, or when it combines with another column. Histograms and current statistics help the optimizer understand skew rather than assuming uniform distribution.

Strategy 3: Use Composite Indexes for Combined Predicates

A composite index stores multiple key columns in a defined order. It is appropriate when important queries routinely use those columns together. The key should model the access path, not merely collect columns that happen to appear in different queries.

For department_id = ? plus a salary range, (department_id, salary) lets the engine navigate to one department and scan its relevant salary interval. Reversing the order changes the physical organization and can make the department condition less useful after the salary range begins.

Strategy 4: Apply the Leftmost Prefix Principle

A conventional multicolumn B-tree is primarily ordered by its first key, then by the second within equal first-key values, and so on. An index on (department_id, city, salary) naturally supports predicates beginning with department, and may support department plus city or all three columns.

A query on city alone generally cannot navigate that ordering as directly. This is a design guideline rather than an absolute rule: an optimizer may scan the index, combine indexes, or use a product-specific skip scan when costs justify it. The execution plan reveals what occurred.

Strategy 5: Start with Equality Before Range

A practical starting rule is to place equality columns before the first range column. The following index groups each customer's orders and orders them by date, supporting a bounded date scan:

CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 201
  AND order_date >= '2026-01-01'
  AND order_date < '2026-02-01';

This is not a universal formula. Required ordering, grouping, skew, multiple query shapes, and engine behavior can justify another order. Test realistic alternatives instead of treating a mnemonic as a law.

Strategy 6: Index Join Columns

Join predicates frequently benefit from indexes on primary, unique, and foreign-key columns. The parent key is usually already indexed by its constraint; the referencing side may need a separate index for child lookup, joins, and parent updates or deletes.

CREATE INDEX ix_employees_department
ON employees (department_id);

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d
  ON d.department_id = e.department_id;

Do not assume every foreign key is automatically indexed across database products. Also remember that a hash join over large inputs may prefer scans, while a selective nested-loop join may gain greatly from an inner-side index.

Strategy 7: Support ORDER BY Deliberately

An ordered B-tree can sometimes return rows in the required sequence and avoid a separate sort. The index ordering must align with equality filters and the requested direction. Modern engines can often scan an index backward, while some support explicit mixed ascending and descending key parts.

CREATE INDEX ix_employees_salary_desc
ON employees (salary DESC);

SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC;

An index does not guarantee sort elimination. If most table rows and many unindexed columns are required, a scan followed by a sort can still be cheaper than repeated row lookups.

Strategy 8: Combine Filtering and Sorting

The strongest ordering indexes often begin with equality predicates and continue with sort keys. An order-history screen filtered by customer and sorted newest first can use (customer_id, order_date DESC). Adding a stable tie-breaker such as order_id DESC also creates deterministic ordering for pagination.

Check every direction in a mixed sort. An index that matches customer_id ASC, order_date DESC may not satisfy a different combination without additional work, depending on the engine.

Strategy 9: Use Covering Indexes Selectively

A covering index contains everything a query needs, allowing an index-only access path when visibility and storage rules permit it. Keep search, join, group, and order columns in the key. Put columns required only for output into nonkey included columns where the database supports that feature.

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

Covering is query-specific and not free. Wide leaf rows reduce cache density, consume storage, and increase write work. PostgreSQL also supports INCLUDE; other engines expose different storage behavior and syntax, so do not copy this statement as portable SQL.

Strategy 10: Avoid Unnecessary SELECT Star

SELECT * makes covering difficult, transfers columns the caller may not need, and couples application code to schema changes. Retrieve the required columns explicitly. A narrower result may let a compact index satisfy the query without visiting the base row and can reduce network traffic even when the plan is unchanged.

Strategy 11: Use Filtered or Partial Indexes

When queries repeatedly target a small, stable subset, indexing only that subset can be more efficient than indexing the whole table. SQL Server calls this a filtered index; PostgreSQL calls it a partial index.

-- SQL Server
CREATE INDEX ix_orders_pending
ON orders (created_at)
WHERE status = 'PENDING';

-- PostgreSQL
CREATE INDEX ix_orders_pending
ON orders (created_at)
WHERE status = 'PENDING';

The query predicate must be compatible with the index predicate. Parameterization and expression differences can affect proof of compatibility, so inspect the deployed plan rather than assuming a textual similarity is enough.

Strategy 12: Use Unique Indexes for Business Rules

A unique index can provide fast lookup while enforcing a business identity under concurrency. Email, employee code, tenant plus username, and external payment reference are common examples. Define the expected case, collation, normalization, null, and soft-delete behavior before implementation.

CREATE UNIQUE INDEX ux_customers_email
ON customers (normalized_email);

Prefer a unique constraint when the rule is a straightforward relational constraint. Use a direct unique index when a product-specific expression or conditional predicate is necessary.

Strategy 13: Index Foreign Keys When Appropriate

An index beginning with the foreign-key columns can accelerate joins, child lookups, and referential checks when a parent row changes. It is especially valuable for high-volume relationships. However, a rarely queried relationship on a small or write-intensive table may not justify another index.

Match the full column order for composite foreign keys and check whether another composite index already provides the required leading prefix.

Strategy 14: Remove Redundant Indexes Carefully

Indexes can overlap. If one index is (customer_id) and another is (customer_id, order_date), the wider index may serve some uses of the first, but not always with the same size, uniqueness, locking, or performance. Included columns, filters, collation, direction, and operator classes can also make apparently similar definitions distinct.

Use workload history and a reversible test before removal. MySQL invisible indexes and comparable product features can help evaluate optimizer behavior without immediately dropping a large structure.

Strategy 15: Avoid Over-Indexing

Every insert must add entries to applicable indexes. Updates may move entries when key or included values change, and deletes must remove them. Additional indexes also increase transaction log volume, replication traffic, backup size, cache pressure, deployment time, and lock exposure during maintenance.

Approve an index only when its measured benefit outweighs these costs. Consolidating overlapping designs is often better than accepting every missing-index suggestion independently.

Strategy 16: Choose Clustered Organization Carefully

Clustered terminology is product-specific. SQL Server permits one clustered rowstore index that defines row organization. InnoDB organizes table data around the primary key. PostgreSQL's CLUSTER rewrites a table according to an index but does not continuously maintain that physical order. Oracle offers index-organized tables.

Where a clustering key is chosen, prefer a stable, narrow, and commonly accessed key. Random or frequently changing wide keys can increase page splits, secondary-index size, and write amplification, depending on the engine.

Strategy 17: Design Nonclustered and Secondary Indexes

Secondary indexes provide alternative access paths without changing the table's primary organization. Their row locator depends on the engine: it may be a clustered key, tuple identifier, or row address. This affects index width and lookup cost.

Keep key columns focused on search and ordering. Add output columns only when a well-used query benefits enough to justify the larger leaf structure.

Strategy 18: Use Full-Text Indexes for Linguistic Search

A B-tree is not designed for relevance-ranked word, phrase, stemming, and proximity search inside long documents. Use the database's full-text subsystem for that requirement. Keep exact IDs, dates, categories, and authorization filters in ordinary indexes and combine them with text results.

Strategy 19: Consider Columnstore for Analytics

Column-oriented indexes can compress repeated values and process large scans and aggregations efficiently. They are useful for fact tables, historical reporting, and analytical workloads that touch a few columns across many rows. Their design and update behavior differ from B-trees, and the exact capability is product-specific.

Do not replace selective OLTP lookup indexes blindly. Many production systems combine row-oriented structures for transactions with columnstore structures for reporting.

Strategy 20: Index Dates with Real Query Shapes

Date columns are valuable when queries use bounded ranges and the ranges are selective. In multitenant systems, tenant or account often precedes time. Append-heavy logs may benefit from time-oriented partitioning or compact range indexes rather than many wide B-trees.

CREATE INDEX ix_events_account_time
ON events (account_id, occurred_at);

SELECT event_type, occurred_at
FROM events
WHERE account_id = 42
  AND occurred_at >= '2026-11-01'
  AND occurred_at < '2026-12-01';

Strategy 21: Write SARGable Predicates

A search-argument-capable predicate exposes an indexed column directly to an operation the index can support. Wrapping that column in a function often prevents a normal range seek unless an appropriate expression index exists.

-- Less index-friendly
WHERE YEAR(order_date) = 2026

-- Range form
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01'

The range form also handles timestamps correctly and avoids listing every possible time value within the year.

Strategy 22: Avoid Implicit Type Conversion

Comparing incompatible types can force conversion on the indexed column, distort cardinality estimates, or prevent an efficient seek. Bind parameters using the same type, length, precision, scale, and Unicode semantics as the column. Execution plans often expose conversion warnings or converted predicates.

Strategy 23: Optimize Pagination

Large OFFSET values may require the engine to read and discard many earlier rows. Keyset pagination starts after the last observed ordered key and can use a matching composite index efficiently.

CREATE INDEX ix_orders_created_id
ON orders (created_at DESC, order_id DESC);

SELECT order_id, created_at, total_amount
FROM orders
WHERE (created_at < :last_created)
   OR (created_at = :last_created AND order_id < :last_id)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST 50 ROWS ONLY;

Tuple comparison can express this more compactly in supporting databases. Always include a unique tie-breaker for stable page boundaries.

Strategy 24: Support GROUP BY Where Beneficial

An index ordered by filtering and grouping keys can reduce rows early or avoid some sorting. It does not make aggregation free, especially when most rows participate. Compare a streaming or ordered aggregate with hash aggregation and parallel scans using realistic cardinality.

Strategy 25: Evaluate DISTINCT as a Data Signal

An index can help produce distinct values in key order, but frequent DISTINCT may also hide an incorrect join or duplicate data model. Fix semantic duplication first. Add an index only when deduplication is legitimate, frequent, and measurably expensive.

Strategy 26: Use Expression Indexes

If the application intentionally searches a deterministic expression, index that expression using the product's supported mechanism. PostgreSQL and Oracle support expression or function-based indexes; SQL Server commonly uses an indexed computed column; MySQL can use functional key parts or generated-column indexes.

-- PostgreSQL example
CREATE INDEX ix_customers_lower_email
ON customers (LOWER(email));

SELECT customer_id
FROM customers
WHERE LOWER(email) = LOWER(:email);

The query expression must match the indexed semantics, including collation and data type. For case-insensitive identity, normalization at write time plus a unique rule may be clearer.

Strategy 27: Index Sparse Subsets

Sparse data often benefits from a conditional index on rows where a value is present or actionable. Examples include unprocessed jobs, unresolved incidents, unshipped orders, and non-null external references. The subset should remain meaningfully smaller than the table and correspond to a stable query pattern.

Strategy 28: Combine Partitioning and Indexing

Partitioning divides data for pruning, maintenance, retention, and manageability; it is not a substitute for indexes inside the selected partitions. Align partition keys with common elimination predicates when practical, and then design local or global indexes according to the database's capabilities and operational requirements.

Validate that the plan actually prunes partitions. A function, mismatched type, or missing predicate can turn an intended targeted operation into a scan across every partition.

Strategy 29: Select Specialized Index Types

Use a structure suited to the operator family. PostgreSQL provides B-tree, hash, GIN, GiST, SP-GiST, and BRIN indexes. Other engines offer spatial, XML, JSON, bitmap, hash, and full-text structures. A general B-tree remains the default for equality, range, and ordering, but it should not be forced onto every domain.

Strategy 30: Maintain Accurate Statistics

Statistics describe row counts, distinct values, distributions, and correlations that help the optimizer estimate cardinality. An excellent index may be ignored when statistics are stale or insufficient. Ensure automatic collection is healthy, and use targeted higher-resolution or multicolumn statistics when skew and correlation cause persistent errors.

Strategy 31: Monitor Index Usage

Track seeks, scans, lookups, write counts, size, and last-use information using engine-specific views and observability tools. Interpret counters over a representative business cycle. A monthly close index may look unused during an ordinary week, while an index used by a critical incident workflow can be essential despite low frequency.

Strategy 32: Read Execution Plans

Execution plans show whether the engine seeks, scans, sorts, hashes, performs key lookups, combines indexes, or applies residual predicates. Compare estimated and actual rows to locate cardinality problems. A scan can be correct for a small table or broad query; a seek followed by millions of random lookups can be worse.

-- Syntax varies by database
EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;

-- PostgreSQL runtime example
EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;

Strategy 33: Measure Before and After

Capture a baseline before creating an index. Repeat the same representative workload after creation and compare logical reads, physical reads, CPU, elapsed time, rows examined, memory grants, temporary work, lock duration, and write throughput. Warm-cache and cold-cache tests answer different questions, so document the method.

MetricWhat it revealsPossible tradeoff
Logical readsPages examined from memoryA covering index may reduce reads but grow writes
CPU timeProcessing and comparison workCompression may exchange I/O for CPU
Elapsed timeUser-observed durationConcurrency and waits influence results
Write latencyIndex maintenance impactRead gains can slow transactions

Strategy 34: Understand Fragmentation

Page splits, deletes, and random modifications can leave some index structures less dense or less physically ordered. The impact depends on storage, access pattern, cache, scan behavior, and engine architecture. A fragmentation percentage without workload context does not prove that rebuilding will improve performance.

Strategy 35: Rebuild or Reorganize Selectively

Maintenance can consume I/O, CPU, log space, locks, replication bandwidth, and temporary storage. Rebuild, reorganize, vacuum, reindex, or compact only when the database's evidence and operational guidance justify it. Coordinate statistics updates, online capabilities, recovery space, and failover replicas.

A fixed nightly rebuild of every index is usually less defensible than a targeted policy based on size, change rate, measured degradation, and maintenance budget.

Strategy 36: Design for Read-Heavy Systems

Read-heavy systems can tolerate more secondary indexes when they materially reduce latency and I/O. Prioritize the highest-volume filters, joins, ordering, and lookup screens. Cover stable, narrow projections, and consider read replicas or analytical structures when reporting competes with transactions.

Strategy 37: Design for Write-Heavy Systems

Write-heavy systems require restraint. Keep keys narrow, remove duplicate structures, avoid indexing volatile columns without evidence, and benchmark sustained insert and update throughput. Append-oriented tables may benefit from sequential keys, time partitioning, and compact range-oriented indexes.

Measure transaction-log generation, page split behavior, lock waits, replication lag, and storage growth, not only single-row insert duration.

Strategy 38: Balance Mixed Workloads

Mixed systems need explicit service goals. A customer checkout and an administrative report should not silently compete under one unexamined index policy. Identify critical transactions, isolate heavy reporting where appropriate, and choose indexes that improve the dominant paths without destabilizing writes.

Review peak periods separately. An index that helps daytime reads but causes unacceptable overnight batch-update cost may require a different design or execution architecture.

Real-World E-Commerce Strategy

An e-commerce design often uses unique indexes for SKU and normalized customer email, a composite order-history index on (customer_id, order_date DESC), a small conditional index for pending fulfillment, and full-text search for product descriptions. Inventory and payment tables need narrow keys and careful write-cost testing because they change frequently.

Real-World Banking Strategy

Banking workloads prioritize integrity, predictable latency, auditability, and concurrency. Candidate indexes include unique transaction references, account plus posting time, and status plus processing time for operational queues. Sensitive reporting should use authorized access paths, and every change must be tested for locking, logging, recovery, and replica impact.

Software Testing Database Strategy

A test-management platform may index execution ID plus status, project plus creation time, defect severity plus state, and case ID relationships. Dashboard queries often need composite indexes aligned with project and release filters. Large logs and attachments should use appropriate text or external storage strategies rather than widening every operational index.

Employee Management Strategy

An employee system commonly needs unique employee code and email rules, department and status filters, manager relationships, and time-bounded payroll or attendance queries. Indexes should reflect access scope such as organization or tenant first, followed by the local lookup or date key.

Content Platform Strategy

A publishing platform combines ordinary indexes for site, state, author, category, and publication time with full-text search for titles and bodies. A partial index on scheduled or published content can keep operational queues compact. Authorization and tenant filters must remain enforced even when text search supplies candidate document IDs.

Cross-Database Feature Guide

RequirementExample capabilityImportant note
Cover output columnsSQL Server or PostgreSQL INCLUDESyntax and index-only rules differ
Index a subsetSQL Server filtered, PostgreSQL partialQuery predicate must match the condition
Index an expressionPostgreSQL expression, Oracle function-basedDeterminism and expression matching matter
Test index removalMySQL invisible indexPrimary indexes cannot be made invisible
Later composite key accessOracle index skip scanCost-based and data-distribution dependent
Large analytical scansColumnstore or product equivalentNot a universal replacement for OLTP B-trees

Common Indexing Mistakes

  • Creating an index for every column or every query suggestion.
  • Ignoring composite key order and predicate shape.
  • Using wide covering indexes without measuring write cost.
  • Assuming low-cardinality columns are always useless.
  • Keeping overlapping indexes without usage evidence.
  • Using functions or implicit conversions on indexed columns unintentionally.
  • Judging performance from a tiny development dataset.
  • Rebuilding all indexes on a calendar without measured need.
  • Accepting a faster query while ignoring slower writes and larger logs.
  • Failing to document the query and business operation each index supports.

Indexing Best Practices

Begin with business-critical and resource-intensive queries. Review predicates, joins, sort and group requirements, result width, frequency, and concurrency. Design the narrowest structure that supports the access path, use uniqueness for real integrity rules, and select specialized indexes only for operators they are built to serve.

Validate actual plans and runtime metrics using representative data. Keep statistics healthy, monitor usage over a complete business cycle, test write overhead, document ownership, and revisit the design after schema, distribution, or query changes. Treat index maintenance as an evidence-based operational task.

Interview Questions and Answers

What is an indexing strategy?

It is a workload-based plan for selecting, ordering, validating, and maintaining the smallest effective set of indexes while balancing reads, writes, storage, and operations.

Why does composite column order matter?

A B-tree is ordered by leading keys first. Equality, range, ordering, and grouping predicates therefore gain different access paths when the key order changes.

What is a covering index?

It contains all columns needed by a query, potentially avoiding a base-row lookup. Coverage is query-specific and increases index size and modification cost.

Why might the optimizer ignore an index?

The query may return too many rows, the table may be small, statistics may be inaccurate, the predicate may be nonsargable, types may differ, or another plan may have a lower estimated cost.

Is an index scan always bad?

No. A scan can be the cheapest correct plan for broad queries, small tables, analytical processing, or compact covering indexes.

How do you prove an index is useful?

Compare representative execution plans and runtime metrics before and after the change, including reads, CPU, elapsed time, result correctness, write latency, and storage.

Practical Exercises

  1. Create the employee table and populate enough skewed data for meaningful plans.
  2. Compare a city scan with an indexed city lookup.
  3. Test both orders of a department and salary composite index.
  4. Create an index for a join and examine the chosen join algorithm.
  5. Design an index for filtering plus descending date ordering.
  6. Compare a covering index with a lookup-based plan.
  7. Create a partial or filtered index where supported.
  8. Rewrite a function-wrapped date predicate as a range.
  9. Compare offset and keyset pagination at a deep page.
  10. Measure insert throughput before and after adding several secondary indexes.
  11. Identify overlapping indexes and test a reversible removal strategy.
  12. Write an index design document for an e-commerce order workflow.

Quick Reference

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

-- Filtering plus stable ordering
CREATE INDEX ix_orders_customer_recent
ON orders (customer_id, order_date DESC, order_id DESC);

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

-- PostgreSQL or SQL Server conditional index syntax
CREATE INDEX ix_orders_pending
ON orders (created_at)
WHERE status = 'PENDING';

-- Validate rather than assume
EXPLAIN SELECT *
FROM orders
WHERE customer_id = 201;

Key Takeaway

The best SQL indexing strategy begins with measured workload requirements, not with index count. Frequently used selective filters, joins, ordering, and grouping reveal candidate access paths. Composite key order, equality and range behavior, coverage, conditional subsets, and specialized index types determine how well those paths serve real queries.

Every benefit has a cost in storage, memory, writes, logging, deployment, and maintenance. Validate candidates with representative data, actual execution plans, runtime metrics, and concurrency tests. Keep statistics current, monitor usage over meaningful time, remove redundancy carefully, and redesign when query patterns change. A successful strategy is the smallest understandable index portfolio that meets both read and write service goals.