SQL indexes: what every full-stack developer should know

Before becoming a full-stack developer, I started in business intelligence: stored procedures, reports, large databases. That experience taught me something many front-end developers learn late: most application performance problems are solved in the database, often with a simple index.

With ORMs like Prisma, we rarely write SQL by hand. But the ORM does not create indexes for you. Here is what you need to know.

What an index is

Without an index, to find a customer's orders the database reads every row of the table: a sequential scan. On 1,000 rows it is instant; on 10 million, it takes seconds.

An index is a side structure, most often a B-tree, that keeps a column's values sorted with a pointer to each row. Like a book's index, it takes you straight to the right page.

CREATE INDEX idx_orders_customer ON orders (customer_id);

With Prisma, you declare it in the schema:

model Order {
  id         String   @id @default(cuid())
  customerId String
  status     String
  createdAt  DateTime @default(now())

  @@index([customerId, createdAt])
}

Composite indexes and the prefix rule

An index on several columns (customer_id, created_at) is sorted by customer first, then by date. So it serves:

• queries filtering on customer_id;
• queries filtering on customer_id and sorting or filtering on created_at;
• but not queries filtering only on created_at, just as a phone book sorted by last name cannot be used to search by first name.

Rule of thumb: put equality-filtered columns first, then columns used for ranges or sorting.

Queries that prevent index use

• A function applied to the column: WHERE LOWER(email) = 'a@b.com' cannot use an index on email. Fix: an expression index (CREATE INDEX ... ON users (LOWER(email))) or store the normalized value.
• A `LIKE` starting with a wildcard: LIKE 'smith%' uses the index, LIKE '%smith' does not. For text search, use dedicated tools (full-text search, trigrams).
• Mismatched types: comparing a text column to a number forces a row-by-row conversion.
• An `OR` across different columns: it often prevents using a single index; a UNION or two separate indexes can help.

Reading an execution plan

Do not guess: ask the database what it does. In PostgreSQL, EXPLAIN ANALYZE runs the query and shows the actual plan; SQL Server shows the actual execution plan in its management tools.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 'c_123'
ORDER BY created_at DESC
LIMIT 20;

-- Before: Seq Scan on orders  (actual time=0.02..812.4 rows=20)
-- After:  Index Scan using idx_orders_customer_created on orders
--         (actual time=0.03..0.09 rows=20)

Signals to look for: a Seq Scan on a large table, a big gap between estimated and actual row counts, and an expensive Sort step that a well-ordered index would have avoided.

Covering indexes

If the index contains every column the query needs, the database does not even have to read the table: that is a covering index. PostgreSQL (since version 11) and SQL Server let you add non-key columns with INCLUDE.

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC)
  INCLUDE (status, total);

The cost of indexes

An index is not free: it takes space and slows down every write (insert, update, delete), since it must be maintained. Index the columns actually used in the WHERE, JOIN and ORDER BY clauses of frequent queries, and drop indexes that statistics show are unused.

Conclusion

A full-stack developer does not need to be a database administrator, but should know that indexes exist, how a composite index is used, which query shapes make it useless and how to read an EXPLAIN. These few notions are enough to turn multi-second pages into instant ones.