SQL Indexing Best Practices: Choose, Use, and Maintain Indexes

An index is a data structure the database maintains alongside a table so it can find rows without scanning the whole table. It is the same idea as the index at the back of a book — instead of flipping through every page, you go straight to the entries you need.

CREATE INDEX idx_customer_name ON customers (customer_name);

SELECT customer_id, credit_limit
FROM   customers
WHERE  customer_name = 'Alice Corp';

Without the index, the database reads every row of the customers table to find the one matching Alice Corp. With it, the database looks up the value in the index and jumps to the matching row. On a small table, that difference is invisible. On a table with millions of rows, it is the difference between milliseconds and seconds.

The cost is on the write side. Every INSERT, UPDATE, or DELETE has to maintain the index in addition to the table. A table with three indexes means every write does four times the work. Indexes are a trade — reads get faster, writes get slower, and storage grows.

The reason they matter: on almost every real database, indexes are the single biggest performance lever a developer has. A query that scans a hundred million rows versus a query that reads three rows is a difference measured in orders of magnitude, and the index is what makes it possible.

Five types cover most use cases. Each is designed for a specific shape of query.

-- Single-column index
CREATE INDEX idx_customer_name ON customers (customer_name);

-- Composite (multi-column) index
CREATE INDEX idx_region_limit ON customers (region, credit_limit);

-- Unique index
CREATE UNIQUE INDEX idx_customer_name_unique ON customers (customer_name);

-- Full-text index (SQL Server)
CREATE FULLTEXT INDEX ON products (description) KEY INDEX pk_products;

-- Spatial index (PostgreSQL with PostGIS)
CREATE INDEX idx_location ON stores USING GIST (location);

Single-column indexes are the simplest form — one column, one index. Useful for frequently queried single columns.

Composite indexes cover multiple columns. They help when queries filter on both columns together. The column order matters — the index is primarily sorted by the leading column.

Unique indexes enforce uniqueness in addition to speeding up lookups. Business rules like "no two customers can have the same email" are enforced by a unique index.

Full-text indexes are for text search across large string columns. They handle word-based queries efficiently where a normal index cannot.

Spatial indexes handle geographic data — points, lines, polygons. Used for queries like "find every store within ten kilometres".

In practice, single-column and composite B-tree indexes cover 90% of real-world indexing needs. Full-text and spatial indexes show up when the data has a specific shape. Unique indexes are everywhere because they back business rules.

The most important factor is how often the column appears in queries. A column that filters, joins, or sorts frequently is a candidate. A column that is never queried is not.

-- Frequently filtered column — good candidate for an index
SELECT * FROM orders WHERE customer_id = 'C001';

-- Rarely filtered column — likely not worth indexing
SELECT * FROM orders WHERE order_status = 'Pending';

Beyond frequency, three factors matter:

• Cardinality. Columns with many distinct values are more selective and index better. Columns with few distinct values (like a status column with three options) benefit less.
• Update frequency. Columns that change often make the index expensive to maintain. A column that is set once at insert is cheap to index; a column that changes on every write is expensive.
• Column width. Narrow columns make smaller indexes, which fit in memory better and take less storage. Very wide text columns are usually better served by a full-text index.

The trade-off is always between the read benefit and the write cost plus storage. An index that speeds up a query by 100x but slows every insert by 20% might be worth it on a read-heavy table and a poor choice on a write-heavy one.

The practical rule: index for the queries that actually run, not the ones that might. Use query logs and metrics to see what real queries are doing, and index based on that evidence rather than guesses.

Large databases make the indexing trade-offs more visible. Three practices cover most of what matters.

Index the columns that are actually queried. Look at the production query workload. The columns in WHERE, JOIN, and ORDER BY clauses are the candidates. Everything else is a guess.

-- If this is the query that runs most often
SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id
WHERE  o.order_status = 'Delivered';

-- Then index orders.customer_id (join column)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- And possibly orders.order_status if it is highly selective
CREATE INDEX idx_orders_status ON orders (order_status);

Do not over-index. Every index slows down writes. On a table with heavy insert traffic, three well-chosen indexes beat ten mediocre ones. When you are tempted to add an index, first check whether an existing index already covers the query — a composite index on (region, credit_limit) makes a simple index on region unnecessary.

Use composite indexes wisely. A composite index on the right columns can replace two single-column indexes and be more selective. The rule: put the most selective column first, or the column that appears in the most queries first.

Beyond those three, the maintenance side matters too. Review index usage periodically — the database can tell you which indexes are being used and which are not. Drop the ones that no query hits. Rebuild fragmented indexes. Keep statistics current after bulk operations. On large databases, this maintenance is an ongoing responsibility, not a one-time setup.

Three disadvantages come up regularly. They are real costs, not minor caveats.

Writes get slower. Every INSERT, UPDATE, or DELETE has to update every index on the table. On a table with five indexes, a single insert becomes six writes. On write-heavy systems, that overhead can be the dominant cost.

-- One INSERT with three indexes on the table
INSERT INTO customers (customer_id, customer_name, region, credit_limit)
VALUES ('C008', 'Henry Co', 'East', 45000);
-- That statement updates: the table row + idx_customer_name + idx_region + idx_credit_limit

Storage grows. Indexes take space. On a table with a billion rows, a single index can be tens of gigabytes. Multiple indexes on the same table multiply that. Storage costs add up, and on distributed databases where storage is priced per gigabyte, this is a real bill.

Maintenance overhead. Indexes fragment over time and need periodic rebuilds. Statistics need refreshing. Old indexes accumulate and need to be dropped. All of that is ongoing work that has to be scheduled and monitored.

Those three reasons are why the guidance is "index what queries actually need" rather than "index everything that might be useful". Every index is a commitment — storage, write overhead, and maintenance — and it should pay for itself in read performance.

The query optimizer picks based on cost. It estimates how expensive each candidate plan will be and picks the cheapest one. The estimate depends on statistics, index availability, and the shape of the query.

EXPLAIN
SELECT customer_id, customer_name
FROM   customers
WHERE  region = 'North' AND credit_limit > 50000;

That EXPLAIN shows the plan the optimizer chose. The output might say it used idx_region_limit, or scanned the table, or something else — depending on what it estimated would be fastest.

The cost estimate for each candidate plan factors in:

• Index selectivity — how many rows the index will return. A highly selective index returns few rows and is cheap. A low-cardinality index returns many and may be more expensive than a scan.
• Table size — how many rows the table has and how many pages it takes up.
• Index size — the index that fits in memory is cheaper than one that does not.
• Statistics freshness — the optimizer trusts the statistics. Stale statistics produce wrong estimates and bad plans.
• Query complexity — multi-join queries have many possible plans, and the optimizer considers combinations.

When a query runs slowly, the first step is EXPLAIN. If the plan looks wrong — a full scan where an index lookup should be, a hash join where a nested loop would be faster — the fix is usually refreshing statistics, adding a missing index, or rewriting the query so the optimizer sees a better path.

Every database has a slightly different optimizer with its own quirks. MySQL's optimizer, PostgreSQL's, SQL Server's, and Oracle's all take slightly different approaches and produce different plans for the same query. That is why hints and platform-specific tuning exist — sometimes the optimizer gets it wrong even with good statistics.

Fragmentation is the drift between the physical order of index pages and the logical order they should be in. It happens naturally as rows are inserted, updated, and deleted over time.

Imagine a sorted list of names stored on paper cards. Inserting a new name in the middle means shuffling cards around. Do this repeatedly and the physical order of the cards no longer matches the sorted order — some cards are out of place, some pages are partly empty. Reading the list is slower because you have to skip around. That is fragmentation.

-- Check fragmentation
SELECT * FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'DETAILED');

-- Fix it with rebuild
ALTER INDEX idx_customer_name REBUILD;

-- Or with reorganize
ALTER INDEX idx_customer_name REORGANIZE;

Rebuild drops the fragmented structure and builds a fresh compact one. Thorough but resource-intensive.

Reorganize reorders the pages in place without rebuilding from scratch. Lighter but does less.

The rule of thumb: fragmentation under 5% is fine, 5–30% gets a reorganize, and 30%+ gets a rebuild.

To prevent fragmentation in the first place, use a fill factor:

CREATE INDEX idx_customer_name
ON     customers (customer_name)
WITH   (FILLFACTOR = 80);

That leaves 20% free space on each index page at build time. New inserts fill the free space before a page split is needed. Fewer splits means slower fragmentation.

Schedule maintenance for off-peak hours. A rebuild locks the index (unless the database supports online rebuilds), so running it during peak traffic can slow everyone else down.

A covering index contains every column a query needs. The database can answer the query entirely from the index without ever touching the table, which is the fastest kind of lookup you can get.

CREATE INDEX idx_covering_customer
ON customers (customer_name, region, credit_limit);

SELECT customer_name, region, credit_limit
FROM   customers
WHERE  customer_name = 'Alice Corp';

That query needs three columns: customer_name for the filter, region and credit_limit for the output. All three are in the index. The database reads the index entry, extracts the three values, and returns the row — no table access.

With a normal non-covering index, the query would need two reads: one to find the matching index entry, another to fetch the missing columns from the table. The covering index halves the work.

Use covering indexes when a specific query runs very frequently. Dashboard queries and report queries are the classic candidates — they have a fixed column set and often run thousands of times per hour. On a large table, the difference between one read and two reads per execution adds up fast.

-- SQL Server syntax for including columns without adding to the sort key
CREATE INDEX idx_covering_customer
ON customers (customer_name) INCLUDE (region, credit_limit);

SQL Server's INCLUDE clause adds columns to the leaf level of the index without making them part of the sort key. That is useful when the extra columns are only needed for output, not for filtering or sorting.

The trade-off is storage. A covering index is wider than a normal index because it contains more columns. On a large table, that can be significant — a covering index on five columns takes roughly the same space as a copy of those five columns. And writes are slower because every column in the index is updated on every relevant write. Use covering indexes where the query frequency justifies the cost.

Every index on a table has to be updated whenever the table changes. That is the cost side of indexing, and it multiplies with every index on the table.

-- Table with three indexes
INSERT INTO customers (customer_id, customer_name, region, credit_limit)
VALUES ('C008', 'Henry Co', 'East', 45000);
-- Cost: 1 table write + 3 index writes = 4 writes total

That multiplication is the main reason over-indexing is a problem. A table with ten indexes does eleven writes per insert instead of one. On a system that receives thousands of inserts per second, that difference is the difference between a healthy system and a bottleneck.

Each operation is affected differently:

• INSERT — every index gets a new entry. The cost is uniform across all indexes.
• UPDATE — only the indexes that cover the updated columns are touched. An update to credit_limit only updates indexes that include that column. An update to customer_name only touches indexes that include customer_name.
• DELETE — every index gets an entry removed. Same uniformity as INSERT.

That asymmetry between UPDATE and INSERT/DELETE is worth remembering. If most of your writes are updates and they only touch a few columns, indexing many other columns is cheaper than it looks. But if you are inserting new rows constantly, every index is a cost.

The practical approach: match the index count to the read-write ratio of the table. Read-heavy tables (reference data, lookup tables) can afford many indexes. Write-heavy tables (logs, events, order history) should have very few — usually just the primary key and maybe one or two critical lookup columns.

The distinction comes down to where the data lives. A clustered index stores the table's rows in the order of the index — the table itself is the index. A non-clustered index is stored separately, with pointers back at the table.

-- Clustered index
CREATE CLUSTERED INDEX idx_customer_id ON customers (customer_id);

Once that runs, the rows in customers are physically stored sorted by customer_id. Reading the table in that order is a sequential scan. Reading a range of customer ids is very fast because the rows for the range are physically adjacent.

A clustered index is the table. That is why a table can have only one — rows cannot be physically sorted two ways at once. On most real-world tables, the primary key is the clustered index.

-- Non-clustered index
CREATE INDEX idx_customer_name ON customers (customer_name);

A non-clustered index is a separate structure. It stores the values of customer_name in sorted order, each with a pointer to the row in the table. Looking up a name goes through the index first, then to the table row for the other columns.

That extra step is what makes non-clustered indexes slower than clustered indexes — a lookup is two reads instead of one. But you can have many non-clustered indexes on the same table, and that flexibility is why they are so common.

The choice of clustered key matters more than most people realise. It determines the physical layout of the whole table, so it affects every range query, every sequential scan, and the size of every non-clustered index (which contains a copy of the clustered key). Wide clustered keys waste space in every non-clustered index on the table.

Oracle works slightly differently. Its default table structure is heap-organized, with the primary key stored as a regular unique index. Oracle's INDEX ORGANIZED TABLES behave like SQL Server's clustered indexes, but most Oracle schemas use heap tables.

Complex queries with multiple joins and filters often need composite indexes that cover the columns involved in the query's filters and joins.

SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id
WHERE  c.region = 'North'
  AND  o.order_status = 'Delivered';

That query filters on region, joins on customer_id, and filters on order_status. The indexes that would help:

-- Index for the join column (on the "many" side)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite index combining filter and join columns
CREATE INDEX idx_orders_customer_status ON orders (customer_id, order_status);

-- Index for the customer filter
CREATE INDEX idx_customers_region ON customers (region);

The composite index (customer_id, order_status) is more selective than either column alone. When the database joins customers to orders on customer_id, it can use the composite index to look up matching orders and immediately filter by status without scanning through all of a customer's orders.

The general approach for complex queries:

• Index every join column, especially on the "many" side of a one-to-many.
• Combine frequently co-occurring filter columns into composite indexes.
• Use covering indexes for the whole query if it runs often enough.
• Review the execution plan after the change to confirm the index is being used.

Regular maintenance matters for complex queries too. Statistics drift over time, fragmentation accumulates, and the plan that worked six months ago may not be the best plan today. Rebuild fragmented indexes and refresh statistics on a schedule, and re-check the plan after any significant data volume change.

Cardinality is the number of distinct values in an indexed column. It decides how selective an index is — how much of the table a lookup filters out.

-- High cardinality: 7 distinct values across 7 rows
customer_id → C001, C002, C003, C004, C005, C006, C007

-- Low cardinality: 3 distinct values across 7 rows
region → North, South, North, West, South, West, North

A query filtering on customer_id returns one row out of seven. A query filtering on region returns three rows out of seven. The high-cardinality lookup is much more selective — the index skips 6/7 of the table versus 4/7.

Selectivity is the ratio of distinct values to total rows. High selectivity means each value appears in few rows. Low selectivity means each value appears in many. Indexes on high-selectivity columns are more useful because they filter out more of the table per lookup.

There is a rough threshold: if a column's cardinality is less than about 5% of the total row count, a simple B-tree index on that column probably does not help. The database has to read so many rows anyway that the index lookup overhead outweighs the benefit of skipping rows.

When a column has low cardinality, two options help:

• A composite index that combines the low-cardinality column with a high-cardinality one. The pair is more selective than either value alone.
• A bitmap index, which is designed for exactly this case. Bitmap indexes are covered separately.

The reason this matters in practice: a column with low cardinality (a status flag, a boolean, a small list of categories) is not worth a simple index. Understanding cardinality before creating an index saves you from creating indexes that the optimizer will end up ignoring.

Statistics are the database's internal picture of the data distribution in an indexed column. They tell the optimizer how many distinct values a column has, how many rows per value, and how often NULLs appear.

-- Conceptually, statistics for customers.region look like:
-- North: 3 rows (43%)
-- South: 2 rows (29%)
-- West:  2 rows (29%)
-- Total: 7 rows
-- Distinct values: 3

With that information, the optimizer can estimate the cost of a plan. For WHERE region = 'North', it estimates 3 rows out of 7. That is a large fraction, so a full scan might be cheaper than an index lookup. For WHERE customer_id = 'C001', the statistics say 1 row out of 7, so an index lookup wins.

Statistics are refreshed automatically on a schedule in most databases, but the schedule varies. Some databases refresh hourly, some daily, some only when the table changes by a certain percentage. If you have just loaded a million rows into a table, the statistics may be wildly out of date.

-- Manual refresh (PostgreSQL)
ANALYZE customers;

-- Manual refresh (SQL Server)
UPDATE STATISTICS customers;

-- Manual refresh (Oracle)
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'CUSTOMERS');

Stale statistics are one of the most common causes of a query suddenly running slowly for no apparent reason. The query has not changed, the data has not changed in an obvious way, but the optimizer's estimate is wrong and it is picking a bad plan. Refreshing the statistics often fixes the problem immediately.

The times to refresh statistics manually: after a bulk load, after creating an index on an existing table, when the plan has changed without an obvious cause, and when a query is much slower than its shape suggests.

An index should be dropped when it costs more than it earns. Three situations make that true.

It is never used. Every database tracks which indexes are being read. If an index has zero reads over a long period, it is pure overhead. Drop it. The storage it consumes is freed, and every write to the table gets slightly faster.

-- Oracle: find unused indexes
SELECT index_name, table_name, used
FROM   v$object_usage
WHERE  used = 'NO';

It is made redundant by a composite index. A composite index on (region, credit_limit) already covers queries that filter on region alone. If there is also a simple index on region, the simple one is redundant — the composite index handles both cases. Drop the simple one.

-- Redundant pair
CREATE INDEX idx_region ON customers (region);
CREATE INDEX idx_region_limit ON customers (region, credit_limit);
-- The first index is not needed; the second covers it

Its write overhead outweighs its read benefit. On a high-write table, an index that is used rarely but adds cost to every insert may not be worth keeping. Compare the read time saved against the write time added, and if the write cost is higher, the index is a net loss.

DROP INDEX idx_customer_name;

Before dropping, two things worth checking. First, confirm that no other object depends on it — foreign keys, constraints, materialized views. Some databases refuse the drop if something depends on the index; others drop it and break the dependent. Second, run the queries that used the index and confirm they still perform acceptably without it. Once it is dropped, restoring it means rebuilding, which on a large table can take time.

The good news: dropping an index is reversible in the sense that you can recreate it. Unlike dropping a column, no data is lost. If the drop turns out to be a mistake, recreating the index restores the previous behaviour — it just takes a bit of time on a large table.

A bitmap index stores one bit per row per distinct value. Instead of a pointer per row, it uses a bitmap where each bit tells you whether that row has that value.

-- Conceptually, bitmap index on customers.region:
-- North: 1 0 1 0 0 0 1   (rows C001, C003, C007)
-- South: 0 1 0 0 1 0 0   (rows C002, C005)
-- West:  0 0 0 1 0 1 0   (rows C004, C006)

Lookups are extremely fast because the database is just reading bits. A query WHERE region = 'North' reads one bitmap and finds the matching rows immediately. And when a query filters on multiple bitmap-indexed columns, the bitmaps can be combined with bitwise operations — AND for "both conditions", OR for "either condition" — which is dramatically faster than combining B-tree lookups.

CREATE BITMAP INDEX idx_customer_region
ON customers (region);

Bitmap indexes are ideal when the column has low cardinality — few distinct values. Gender, status flags, boolean columns, small category codes. In a data warehouse with a hundred million rows and a status column with three values, a bitmap index takes very little space and makes filtering by status very fast.

The reason they are not universal: writes. A bitmap index locks a whole block of rows when a single row is updated. In an OLTP system where many sessions concurrently insert and update rows, two writers touching different rows in the same block block each other — even though they are not logically related.

That is why the guidance is: bitmap indexes for data warehouses and read-mostly tables; B-tree indexes for OLTP workloads. Oracle is the database most associated with bitmap indexes, and its documentation explicitly warns against using them on tables that are updated frequently.

One thing not to confuse: some databases use "bitmap" internally as a join algorithm — building temporary bitmaps during a query for a hash or merge join. That is a completely different concept from a bitmap index. If you see "bitmap join" in an execution plan, the table does not necessarily have a bitmap index.