Indexes can dramatically improve query performance — but only when they’re used wisely. Adding indexes to the wrong columns can slow down writes, inflate storage, and even make queries worse. So how do you know which columns deserve an index?

Let’s break it down.

Good Candidates for Indexing

These types of columns are typically strong choices for indexes:

1. Columns Frequently Used in WHERE Clauses

If a column appears often in filters, it’s a prime candidate.

Examples:

  • WHERE status = 'ACTIVE'

  • WHERE customerId = 123

The more selective the column (i.e., it filters down many rows to a few), the more effective the index will be.

2. Columns Used in JOIN Conditions

Indexes on join keys help databases match rows faster across tables.

Examples:

  • user.id = order.userId

  • product.id = orderItem.productId

Foreign key columns are especially good candidates.

3. Columns Used in ORDER BY or GROUP BY

Indexes can help avoid sorting work if the index already stores rows in the required order.

Examples:

  • ORDER BY createdDate

  • GROUP BY country

This is particularly valuable on large tables.

4. Columns Used in High-Read / Low-Write Workloads

Indexes shine in analytical or reporting queries where reads dominate writes.

Examples:

  • dashboards

  • reports

  • search screens

Bad Candidates for Indexing

Not every column benefits from indexing — some can actually harm performance.

1. Columns with Very Low Selectivity

If most rows have the same value, an index adds overhead without improving query performance.

Examples:

  • boolean flags (isActive)

  • “status” columns with only a few values

  • gender fields

The database will still scan many rows.

2. Columns That Change Frequently

Every write must update every index — meaning volatile columns can slow inserts/updates.

Avoid indexing:

  • counters

  • frequently updated timestamps

  • log/event values

3. Very Small Tables

Indexes don’t help when the entire table fits in memory — a full scan is often faster.

If there are only a few hundred rows, skip the index.

4. Columns Not Used in Queries

If a column never appears in WHERE/JOIN/GROUP/ORDER — it doesn’t need an index. Simple rule.

What to Watch Out For

Before adding an index, consider these trade-offs:

Indexes Speed Reads — But Slow Writes

Every insert, update, and delete must maintain the index structure. Too many indexes = laggy writes.

Composite Indexes Require Careful Ordering

An index on (firstName, lastName) is not the same as (lastName, firstName).

Create them in the order queries actually use.

Over-Indexing Wastes Memory & Storage

Indexes consume space — sometimes more than the table itself.

Audit unused indexes periodically.

Use EXPLAIN / Query Plans

Always validate assumptions. Let the database show you whether an index is being used or ignored.

Summary

Indexes are powerful — but they aren’t “free.”
Index columns that are:

  • frequently filtered in WHERE clauses

  • used in JOINs

  • part of ORDER BY / GROUP BY

  • selective and relatively stable

Avoid indexing columns that:

  • have low selectivity

  • change frequently

  • belong to very small tables

  • aren’t used in queries

When in doubt: measure first, index second.