NexisyTech Learningby Nexisy Technologies

Indexing & Query Performance

How indexes turn full table scans into fast lookups — and the other habits that keep queries quick.

Intermediate9 min read3 interview questions

An index is a separate, sorted data structure (usually a B-tree) that lets the database find rows without reading the whole table. It is the biggest single lever for query performance.

Indexes are not free: each one uses storage and slows down inserts and updates. The goal is the smallest set of indexes that serves your real queries.

Diagram

Without index — full scanWith B-tree index — seekWHERE email = 'a@x.com'Read all 10M rowsO(n)Root pagea – mn – zLeaf → row~3 page reads
A B-tree index finds one row in a few page reads instead of scanning millions.

How it works

  1. Find slow queries from the query store, slow-query log or APM traces.
  2. Read the execution plan and look for full scans on large tables.
  3. Add an index that matches the WHERE, JOIN and ORDER BY columns.
  4. Select only needed columns; avoid SELECT *.
  5. Paginate large results with keyset pagination (WHERE id > @last).
  6. Use connection pooling and keep transactions short.
  7. Archive or partition old data so hot tables stay small.

Key concepts

B-tree index
Balanced sorted tree; supports equality, range and ORDER BY. The default in most databases.
Composite index
An index on several columns. Order matters: (tenant_id, created_at) serves filters on tenant, then date.
Covering index
Contains every column a query needs, so the table itself is never read.
Execution plan
The database’s chosen strategy for a query. Read it to see scans, seeks and estimated costs.
Selectivity
How many rows a filter keeps. Indexes help most on selective columns.

Trade-offs

Benefits

  • Orders-of-magnitude faster reads
  • Lower CPU and I/O on the database
  • Enforces uniqueness with unique indexes

Costs

  • Slower writes and more storage per index
  • Too many overlapping indexes confuse the optimiser and waste memory

Use it when

  • Columns used in frequent filters, joins and sorts
  • Foreign keys that are joined on

Common mistakes

  • Wrapping an indexed column in a function (WHERE LOWER(email) = …), which prevents a seek
  • Composite index in the wrong column order
  • Using OFFSET 100000 for deep pagination

Interview questions

Q1You have an index on (last_name, first_name). Does it help WHERE first_name = 'Sara'?
Not efficiently. A composite index is sorted by its first column, so filtering only on the second column usually needs a scan of the index.
Q2Why is OFFSET pagination slow on page 5,000?
The database still reads and discards all earlier rows. Keyset pagination seeks directly to the last seen key.
Q3Why not index every column?
Each index must be updated on every write and takes memory and storage. Unused indexes slow writes without helping any reads.

Related lessons

Data & Storage

The N+1 Query Problem

One query for a list, then one more for every item — a silent performance killer in ORMs.

Intermediate6 min read Data & Storage

Database Scaling & Replication

From one database server to replicas, partitions and shards — in the order you should reach for them.

Intermediate10 min read Foundations

Big-O Notation & Time Complexity

A shared language for how the cost of an algorithm grows as its input grows.

Beginner8 min read