Index Strategies Thought Through, Framework-Agnostic

1. Why index strategy should be thought about independently of the framework

Many developers silently leave index strategy to the ORM or framework in use, assuming that automatically generated indexes on foreign keys are already sufficient. That is a fallacy. An ORM knows the structure of the tables but not the actual access patterns of an application, which columns are combined in WHERE clauses, in what order results are sorted, and how selective individual filters really are. A well-thought-out index strategy emerges from understanding the data structure and the queries, not from framework conventions.

The advantage of a framework-agnostic index strategy is that it transfers to any relational database, whether the application works with a PHP ORM, a Java framework, or plain raw SQL statements. The underlying concepts, selectivity, column order, covering indexes, are properties of the B-tree index itself, not of the framework that generates the SQL statements. This article deals with exactly these concepts, detached from any specific technology.

A second reason for this perspective: frameworks change, migrations between ORMs or even between database systems happen regularly in practice. An index strategy based on fundamental principles rather than framework-specific annotations survives such migrations unchanged, while automatically generated indexes have to be reconsidered with every technology switch.

2. How a B-tree index actually works

The default index in nearly every relational database is a B-tree, a balanced tree structure that keeps values sorted and guarantees logarithmic search times. Every node of the tree holds a set of sorted keys with references to child nodes or, at the leaves, to the actual table rows or their addresses. This sorting is the reason a good index strategy decides not only whether an index exists, but also in what order values are stored within it.

Because a B-tree is sorted, it efficiently supports not only equality comparisons but also range queries such as BETWEEN, less-than, and sorting via ORDER BY, provided the sort direction matches the index direction. This property is central to any index strategy, because a single well-chosen index can handle filtering and sorting at the same time, without the database having to sort separately.

A hash index, which some databases offer as an alternative, is instead suited only for exact equality comparisons and supports neither range queries nor sorting. For most use cases the B-tree therefore remains the default, and every index strategy discussed in this article assumes a B-tree index unless stated otherwise.

-- Standard B-tree index: supports equality, range and sorted access
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

-- This single index efficiently serves all three queries below
SELECT * FROM orders WHERE customer_id = 4821;
SELECT * FROM orders WHERE customer_id = 4821 AND order_date >= '2026-01-01';
SELECT * FROM orders WHERE customer_id = 4821 ORDER BY order_date DESC;

3. Measuring and correctly interpreting selectivity

Selectivity is the central measure of any index strategy: it describes how many distinct values a column has relative to the total number of rows. A column with high selectivity, such as an email address or an order number, narrows a query down to very few rows and benefits strongly from an index. A column with low selectivity, such as a status flag with only three possible values, filters out almost nothing, an index on it often helps little or is even ignored by the optimizer.

The formula for selectivity is simple: number of distinct values divided by total row count. A value close to 1 means high selectivity, a value close to 0 means low selectivity. An index strategy that ignores this metric frequently produces indexes that exist but are not used by the optimizer, because a full table scan at low selectivity is simply cheaper than the additional overhead of index navigation plus table access.

It is important that selectivity should not be treated as static but as context-dependent. A status flag with low global selectivity can still be usefully indexed in combination with a second filter, such as a customer name, if the combination of both conditions narrows the result set significantly. A solid index strategy therefore always considers actual query patterns, not isolated individual columns.

-- Measuring selectivity: distinct values / total rows
SELECT
  COUNT(DISTINCT status) AS distinct_values,
  COUNT(*) AS total_rows,
  ROUND(COUNT(DISTINCT status) * 1.0 / COUNT(*), 4) AS selectivity
FROM orders;
-- Result: 3 distinct values / 500000 rows = selectivity 0.000006, low, avoid a standalone index

SELECT
  COUNT(DISTINCT email) AS distinct_values,
  COUNT(*) AS total_rows,
  ROUND(COUNT(DISTINCT email) * 1.0 / COUNT(*), 4) AS selectivity
FROM customers;
-- Result: 498000 distinct values / 500000 rows = selectivity 0.996, high, index pays off

4. Determining column order in a composite index

For a composite index, an index over multiple columns, the order of the columns largely determines its usability. An index over (a, b, c) can be used efficiently for queries that filter on a alone, a and b, or a, b and c, but not for queries that filter only on b or only on c without including a. This rule, often called the "leftmost prefix rule", is one of the most important foundations of any index strategy involving multiple columns.

A proven rule of thumb for column order is: equality filters first, range filters after, sort columns last. A column that is always compared to an exact value, such as customer_id, belongs at the start of the index. A column compared against a range, such as order_date with a BETWEEN, belongs after it, because a range filter limits the usability of further columns in the index for subsequent equality comparisons. An index strategy that ignores this order often wastes considerable optimization potential.

A common mistake is choosing column order by the selectivity of the individual column rather than by the actual filter type. A highly selective column that is used in a range filter should still come after a less selective, but equality-filtered column. This nuance is what separates an effective index strategy from one that looks theoretically correct but does not deliver the expected performance in practice.

-- Rule: equality columns first, range columns after, sort columns last
-- Query pattern: filter by status (equality), date range, sort by amount
CREATE INDEX idx_orders_status_date_amount
ON orders (status, order_date, total_amount);

-- Fully sargable, uses the index for filter AND sort
SELECT order_id, total_amount
FROM orders
WHERE status = 'shipped'
  AND order_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY total_amount DESC;

-- WRONG order: range column before equality column wastes the composite index
CREATE INDEX idx_orders_date_status_bad
ON orders (order_date, status, total_amount);
-- status can no longer use an efficient equality lookup within the range

5. Using covering indexes deliberately

A covering index contains all the columns a query needs, both in the WHERE clause and in the SELECT part, so the database no longer has to access the actual table after the index lookup. This additional access, called a heap fetch in PostgreSQL, costs significant time when there are many matches, because each row must be read individually from the table, even if the index itself was already scanned sequentially and quickly. An index strategy that deliberately uses covering indexes for frequent, performance-critical queries can drastically reduce response times.

In MySQL and PostgreSQL, a covering index is achieved by including additional columns at the end of the index, either as a regular part of the index or, in PostgreSQL, via the INCLUDE clause, which stores columns in the index without using them for sorting or searching. SQL Server offers the same feature also via INCLUDE. This separation between search columns and carried-along columns is an important tool of any modern index strategy, because it keeps the index compact while still containing all needed data.

Covering indexes are not a cure-all, since every additional column enlarges the index and thereby increases storage requirements as well as the cost of write operations. A sensible index strategy deploys covering indexes deliberately for the most frequent, most performance-critical queries, not blanket-style for every table. The trade-off between read performance and write cost is examined more closely in the later section on write costs.

-- PostgreSQL: INCLUDE keeps extra columns in the index without using them for search
CREATE INDEX idx_orders_covering
ON orders (customer_id, order_date)
INCLUDE (total_amount, status);

-- Fully covered: no heap fetch needed, all columns come from the index
SELECT order_date, total_amount, status
FROM orders
WHERE customer_id = 4821
ORDER BY order_date DESC;

-- MySQL equivalent: append columns directly to the composite index
CREATE INDEX idx_orders_covering
ON orders (customer_id, order_date, total_amount, status);

6. Recognizing redundant and unnecessary indexes

Over time, grown databases often accumulate redundant indexes, for example an index over (a) next to an index over (a, b). The single-column index is usually superfluous in this case, because the two-column index, thanks to the leftmost prefix rule, already serves every query the single-column index would have served. Regularly checking for such redundancies is an often neglected but important part of any index strategy.

Equally problematic are indexes that were once created for a specific query, but whose associated query no longer exists because the application has since changed. Such orphaned indexes continue to cost storage space and slow down every write operation on the affected table, without ever being used for a read operation. Most databases offer system views to identify unused indexes, such as pg_stat_user_indexes in PostgreSQL or sys.dm_db_index_usage_stats in SQL Server.

A good index strategy therefore includes a recurring review process, in which index usage is observed over a sufficiently long period, at least one full business cycle, before an apparently unused index is actually removed. Seasonal query patterns, such as year-end reports, would be wrongly classified as irrelevant with too short an observation period.

7. The write cost of every additional index

Every index speeds up read operations but simultaneously slows down every INSERT, UPDATE and DELETE operation on the affected table, because the database has to keep the index consistent on every change. An index strategy that ignores this trade-off and blindly indexes every potentially useful column leads to noticeable performance losses on write-intensive tables, often exactly where latency is especially critical, such as in order or payment processes.

The write cost effect intensifies further with composite indexes and covering indexes, because more data per index entry has to be written and re-sorted on updates. For a table with ten indexes, a single INSERT means ten additional write operations, not just one. A well-considered index strategy therefore weighs the ratio of read to write load for every table and indexes considerably more cautiously on write-heavy tables than on predominantly read-only reporting tables.

A practical heuristic: for tables with a read-write ratio of at least 10 to 1, almost any index that noticeably speeds up a frequent query is usually worth it. For a balanced or write-heavy ratio, every additional index should be individually justified against its benefit, instead of indexing every potentially helpful column across the board.

8. Partial, function and full-text indexes

Beyond the classic B-tree index over complete columns, modern databases offer specialized index forms that enable a more targeted index strategy. A partial index, natively supported in PostgreSQL, indexes only the rows that satisfy a certain condition, for example only active orders instead of all ever created. This drastically reduces index size and is especially effective when queries almost always concern only a small, clearly defined part of the table.

A function index does not index the raw column but the result of an expression, for example LOWER(email) for case-insensitive searches or EXTRACT(YEAR FROM order_date) for yearly reports. Without such an index, the database cannot use the index on the raw column as soon as a function is applied to it, because the sorted order of the index no longer matches the sorted order of the function results. This observation is one of the most common reasons an apparently existing index is not used in practice.

For text search beyond simple LIKE patterns, PostgreSQL and MySQL offer full-text indexes that index words rather than exact strings and support relevance ranking. A complete index strategy accounts for these specialized index forms wherever standard B-tree indexes reach their limits, such as full-text search, geographic data, or JSON documents with GIN indexes in PostgreSQL.

-- PostgreSQL: partial index, only indexes rows matching the condition
CREATE INDEX idx_orders_active
ON orders (customer_id)
WHERE status IN ('pending', 'processing');

-- Function index for case-insensitive lookups
CREATE INDEX idx_customers_email_lower
ON customers (LOWER(email));

-- This query only benefits from the index if the expression matches exactly
SELECT * FROM customers WHERE LOWER(email) = 'user@example.com';

9. Index types compared directly

Choosing the right index type depends heavily on the query pattern. The following table compares the most important index forms and their typical use cases as a quick reference for your own index strategy.

This overview shows that a complete index strategy rarely gets by with a single index type. The standard B-tree covers the large majority of cases, while specialized index forms are deployed deliberately where B-tree indexes structurally reach their limits.

10. Summary

An effective index strategy is based on principles that remain valid regardless of the framework or ORM in use: prefer high selectivity, order equality columns before range columns in a composite index, deploy covering indexes deliberately for performance-critical queries, and consciously weigh the write cost of every additional index against its read benefit. These principles apply to MySQL just as they do to PostgreSQL or SQL Server, because they are properties of the B-tree index itself.

The biggest mistake in practice is leaving index decisions entirely to the ORM, or creating indexes purely reactively after observed performance problems. A well-thought-out index strategy emerges proactively from understanding an application's actual query patterns, combined with regular checks for redundancy and unused indexes. This knowledge stays valuable even when the framework, the programming language, or even the database system changes in the future.

11. FAQ: Index Strategies Framework-Agnostic