Database Indexing: When to Add One and When It Hurts

An index is a sorted lookup structure the database maintains alongside your table. It makes matching rows fast to find, and it makes every insert and update slightly slower because the index has to be maintained too. That trade-off is the whole topic.

Index What You Filter, Join and Sort On

In practice that means:

• Every foreign key. Laravel's foreignId() adds one; raw unsignedBigInteger does not.
• Columns in frequent WHERE clauses — slug, email, status.
• Columns you ORDER BY on large tables.

Composite Index Column Order Is Not Arbitrary

An index on (is_active, published_at) helps a query filtering on is_active alone, and one filtering on both. It does not help a query filtering only on published_at.

Rule of thumb: equality columns first, range columns last.

$table->index(['is_active', 'published_at']);

Do Not Index Low-Cardinality Columns Alone

A boolean column with a 50/50 split gains nothing from its own index — the database still reads half the table, and now it reads the index too. Combine it with something selective instead.

Read the EXPLAIN Output

EXPLAIN SELECT * FROM blog_posts
WHERE is_active = 1 AND published_at <= NOW()
ORDER BY published_at DESC;

What you want to see: a named index under key, a small number under rows, and no Using filesort on a large table. type: ALL means a full table scan.

The Cost of Too Many

Every index is rebuilt on every write. A table with twelve indexes has slow inserts, a bloated disk footprint, and a query planner with more ways to choose wrong. I have fixed more slow systems by removing indexes than people expect.

Measure, Then Index

Enable the slow query log, find the actual offenders, and index those. Adding indexes speculatively is how you end up with the twelve.