When I was a junior developer, I added 5 indexes to a table thinking it would speed things up. Instead, I made queries 3x slower.

If you have experience with indexes, you know that more indexes != faster queries.

Indexes have costs:

  • Write overhead: Every INSERT/UPDATE maintains all indexes
  • Storage cost: Indexes consume disk space
  • Query planner confusion: Too many options can lead to poor choices

The Right Approach#

1. Index cardinality matters

An index on gender (2 values) helps less than one on user_id (millions of values). High cardinality = better performance.

2. Know your query patterns

Index columns that appear in:

  • WHERE clauses
  • JOIN conditions
  • ORDER BY clauses

Don’t index columns you never query.

3. Composite indexes are powerful

-- Instead of separate indexes on (user_id) and (created_at)
CREATE INDEX idx_user_created ON orders(user_id, created_at);

Order matters. Put the column with highest cardinality first, or column in equality conditions first.

4. Understand index types

  • B-tree: Default, works for most cases (range queries, equality)
  • Hash: Fast equality lookups, no range support

5. Monitor index usage

At Oracle, we found indexes that were never used but slowed every write. Remove unused indexes.

-- MySQL: Find unused indexes (requires Performance Schema)
-- Note: Only shows data since last server restart.
SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes;

The Lesson#

When I removed 3 redundant indexes and optimized 2 others, query performance improved 7x. Writes got faster as well.

Takeaway: Strategic indexing beats random indexing. Analyze your queries, measure impact, and index deliberately.

What’s your indexing war story? Ever removed an index that made everything faster?