SQL and Indexes

The problem: a big table, a slow query

Our shop’s orders table has 50 million rows. On the “My orders” page, the customer sees their orders, newest first:

SELECT TOP (20) Id, CreatedAt, Total, Status
FROM Orders
WHERE CustomerId = @customerId
ORDER BY CreatedAt DESC;

When the table was small, this query was fast. Now it takes a few seconds. Why? Because the database has no way to find the orders of customer 42 directly. So it reads all 50 million rows, one by one. This is called a Scan.

What is an index?

Think of the index at the end of a book. The words are sorted, and next to each word is a page number. You do not need to read the whole book.

A database index is the same. It is a sorted tree (B-Tree) of column values. Each leaf of the tree points to the main row.

Two words you will see a lot in a Query Plan:

1. Seek. The database goes from the root of the tree directly to the right place. It reads only a few pages.
2. Scan. The database reads all rows from start to end. On a big table, it is slow.

For the query above, this index is enough:

CREATE INDEX IX_Orders_CustomerId_CreatedAt
ON Orders (CustomerId, CreatedAt);

Column order matters

An index on two columns is like a phone book: it is sorted first by last name, then by first name. So:

1. Searching by last name is fast. It is the first column of the index.
2. Searching by last name and first name is also fast. Both columns, in order.
3. Searching only by first name is slow. People named “Ali” are spread over all the pages.

It is the same for the orders index. An index on customer id and date is great for “orders of customer 42, sorted by date”. But it does not help for “all orders from yesterday”, because the date is the second column.

Jumping to the main table, and the covering index

In SQL Server, each table usually has a Clustered Index. This means the table itself is stored sorted by the primary key. Other indexes (Nonclustered) are separate. Each leaf has only the index columns and the row’s primary key.

Now our query also wants the Total and Status columns. These columns are not in the index. So the database makes a separate jump to the main table for each row. This is called a Key Lookup.

The fix is a covering index. Add the needed columns to the index leaves with INCLUDE:

CREATE INDEX IX_Orders_CustomerId_CreatedAt
ON Orders (CustomerId, CreatedAt)
INCLUDE (Total, Status);

INCLUDE columns play no part in sorting the tree. They are only stored next to each leaf, so no jump is needed. PostgreSQL also has the same INCLUDE option, since version 11.

In EF Core, you define the same index like this:

protected override void OnModelCreating(ModelBuilder model)
{
    model.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.CreatedAt })
        .IncludeProperties(o => new { o.Total, o.Status });
}

Conditions that make the index useless

The index is built on the raw value of the column. If you apply a function to the column, the database cannot search the tree. It must compute the function for every row. So it scans.

-- Bad: the function on the column hides it from the index
SELECT Id FROM Orders WHERE YEAR(CreatedAt) = 2026;

-- Good: a range on the raw column can use the index
SELECT Id FROM Orders
WHERE CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01';

Similar cases that make the index useless:

1. Text search with a percent sign at the start. The condition “name like percent Ali” does not know where in the tree to start.
2. Hidden type conversion. For example, comparing a varchar column with an nvarchar parameter in SQL Server. The database may have to convert the column for every row.
3. Math on the column. For example, “price times 2 greater than 100”. Instead, write “price greater than 50”.

Read the Query Plan

Do not guess. Ask the database itself what it did:

1. In SQL Server, turn on the Actual Execution Plan. The SET STATISTICS IO ON command also shows the number of pages read.
2. In PostgreSQL, write EXPLAIN ANALYZE in front of the query.
3. Look for these. A scan on a big table. A Key Lookup with many rows. A big gap between the estimated and the actual number of rows.
4. Measure before and after. Prove every index change with numbers, not with a feeling.

An index is not free

Each index is a sorted copy of some columns. So:

1. Writes get slower. Every INSERT, UPDATE and DELETE must also update all the indexes of the table.
2. It takes space. On disk and in the database’s memory.
3. A duplicate index is waste. An index on customer id alone is usually not needed when you have an index on customer id and date.

So do not build an index for every query. Build indexes for frequent and important queries, and check the rest with the Query Plan.

Paging on a big table

The orders list in the admin panel is paged with Skip and Take (in SQL, that is OFFSET and FETCH). The first pages are fast, but page 50 thousand takes a few seconds. Why?

1. Page 50 thousand means “skip 999,980 rows, then give 20”.
2. The database cannot jump directly to row 999,980. Even with an index, it must count from the start.
3. So it reads about one million rows and throws them away.

It has another problem too. If a new order is placed between two pages, all rows shift by one place. The user sees the last order of the previous page again.

The fix is Keyset Pagination. Instead of “which page?”, we say “give me the next 20 after the last row I saw”:

var page = await db.Orders
    .Where(o => o.CreatedAt < lastCreatedAt
             || (o.CreatedAt == lastCreatedAt && o.Id < lastId))
    .OrderByDescending(o => o.CreatedAt).ThenByDescending(o => o.Id)
    .Take(20)
    .ToListAsync(ct);

1. With an index on the creation date and the order id, the database jumps directly to the right place. Page 1 and page 50 thousand have the same speed.
2. The order id is needed, because two orders may have the same creation date. Without it, the order is not unique.
3. A new order at the top of the list does not move our starting point. So no duplicates happen.
4. The cost is that jumping to “page 47” is not possible. We only have next and previous pages. It is great for infinite scroll and APIs.

Read Replica and lag

When read load is high, we add a read-only copy (Read Replica). Writes go to the main database and reads go to the copy. But changes usually reach the copy with a small delay. This is called Replication Lag.

The result: a customer saves their address, the page reloads, and they still see the old address. For this case:

1. Read from the main database for a while after a write. For example, for a few seconds after saving, send that user’s reads to the main database.
2. Do not read important decisions from the copy. For example, checking the stock before decreasing it.
3. Monitor the lag and set an alert for it.

Common mistakes