Arunangshu Das Blog

A slow page often boils down to one thing: the database is scanning too many rows. An index fixes that by giving the database a shortcut to the rows you need.

It’s like the index at the back of a book; instead of flipping every page, the database jumps straight to matching rows. Under the hood, engines use structures like B-trees or hash tables to make lookups fast.

Why it matters

• Faster queries: Avoids full table scans on large datasets.
• Better sorting/filtering: Speeds up WHERE, ORDER BY, and GROUP BY.
• Faster joins: Matching keys across tables is quicker with the right indexes.
• Data integrity: Unique indexes prevent duplicates where they shouldn’t exist (e.g., emails).

When to add an index

• Columns are often used in WHERE, JOIN, or sorting.
• Use a composite index for multi-column filters that appear together.
• Keep the set lean and review as query patterns change.

If you’ve ever added lots of single-column indexes and still saw slow queries, this explainer on mistakes helps: 10 common mistakes in Database Indexing.

But, the trade-off is that indexes take disk space and make writes slower because inserts/updates must also update the index. They also need occasional maintenance. So don’t index everything, but only what your app actually uses.

If it’s still slow after indexing, look at bigger moves like caching, read replicas, or scaling the database vertically or horizontally.

Start with your slow query. If it filters/joins on certain columns, index them (and use a composite index when filters combine). Keep indexes lean. If performance is still an issue, move to caching and scaling. ​

Read the full breakdown of why database indexing is crucial​

Thanks,
Arunangshu