DK Damir Khaibulin
🇷🇺 RU Russian 🇬🇧 EN English 🇩🇪 DE German 🇫🇷 FR French 🇮🇹 IT Italian 🇪🇸 ES Spanish
Let's talk
← Back to articles
Performance & Databases

Database Indexes: The Invisible Superheroes of Your App. Explained Simply with Laravel Code and Important Warnings

A clear guide to database indexes with Laravel examples: how they work, when to add them, and the critical warnings every developer must know about composite, low-selectivity, and unused indexes.

Let's talk ↗ Read Article
Available languages
EN RU DE FR IT ES
✦
Featured article ↗

Database Indexes: The Invisible Superheroes of Your App. Explained Simply with Laravel Code and Important Warnings

Imagine searching for a specific word in a 1,000-page book — but the book has no table of contents and no index at the back. You'd have to read every page. That's exactly what your database does when it runs a query on a column without an index.

Indexes are the invisible superheroes of your application. When they're doing their job, nobody notices. When they're missing, everything grinds to a halt. Let's break down what they are, how they work, and — most importantly — the warnings every developer must know.

📚 What is a Database Index?

A database index is a special data structure (usually a B-tree) that the database maintains alongside your table data. It stores a sorted copy of one or more columns, plus pointers back to the original rows.

Think of it as the alphabetical index at the back of a textbook: instead of scanning every page, you jump straight to the relevant entry.

🎯 Why Indexes Matter

  • Speed up WHERE clauses — finding rows by a condition becomes O(log n) instead of O(n).
  • Speed up JOIN operations — foreign keys benefit massively from indexes.
  • Speed up ORDER BY — if the sort matches an index, no additional sort is needed.
  • Enforce uniqueness — unique indexes guarantee data integrity at the storage level.

Without an index, MySQL/PostgreSQL must do a full table scan. On a table with millions of rows, that's the difference between 5ms and 5 seconds.

🛠 Creating Indexes in Laravel

Laravel's migration system makes index creation clean and expressive.

1. Index on a single column:

Schema::table('users', function (Blueprint $table) { $table->index('email'); // Simple index });

2. Unique index:

Schema::table('users', function (Blueprint $table) { $table->unique('email'); // Enforces uniqueness });

3. Composite index (multiple columns):

Schema::table('orders', function (Blueprint $table) { $table->index(['user_id', 'created_at']); // Composite index });

4. Index with a custom name:

Schema::table('posts', function (Blueprint $table) { $table->index('status', 'idx_posts_status'); });

5. Foreign key with automatic index:

Schema::table('posts', function (Blueprint $table) { $table->foreignId('user_id')->constrained(); // Creates index automatically });

🔍 How to Verify an Index Is Being Used

Use EXPLAIN to see the query plan:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com'; -- Output (index being used): -- Index Scan using users_email_index on users (cost=0.43..8.45 rows=1 width=...) -- Index Cond: ((email)::text = 'user@example.com'::text) -- Output (no index, full scan): -- Seq Scan on users (cost=0.00..1500.00 rows=1 width=...) -- Filter: ((email)::text = 'user@example.com'::text)

In Laravel:

$plan = DB::select('EXPLAIN SELECT * FROM users WHERE email = ?', ['user@example.com']); dd($plan);

Look for Index Scan or Index Only Scan — good. Look for Seq Scan — bad, unless the table is tiny.

⚠️ Important Warnings Every Developer Must Know

1. Indexes Slow Down Writes

Every INSERT, UPDATE, and DELETE must also update every index on that table. A table with 10 indexes performs writes roughly 10x slower than a table with none. Index only columns you query, not columns you imagine you might query.

2. The "Leftmost Prefix" Rule for Composite Indexes

An index on (a, b, c) can be used for queries on a, (a, b), or (a, b, c). It cannot be used efficiently for queries on b alone or (b, c). Order matters.

// This uses the composite index (user_id, created_at): SELECT * FROM orders WHERE user_id = 5 AND created_at > '2024-01-01'; // This does NOT use it efficiently: SELECT * FROM orders WHERE created_at > '2024-01-01';

3. Indexes on Low-Selectivity Columns Are Useless

A boolean column with 99% true and 1% false is a poor index candidate. The optimizer will often ignore it and do a full scan anyway, because scanning the index plus looking up rows costs more than just scanning the table.

4. Functions on Indexed Columns Kill the Index

// BAD — index on `email` is NOT used: SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; // GOOD — index on `email` IS used: SELECT * FROM users WHERE email = 'user@example.com';

Solutions: store normalized data, or use functional/expression indexes (PostgreSQL supports them; MySQL 8+ supports them).

5. Too Many Indexes Are as Bad as Too Few

Each index takes disk space and slows down writes. More importantly, the optimizer has to consider more options when planning a query, which can lead to worse decisions. Audit your indexes regularly.

6. Unused Indexes Are Pure Overhead

Find indexes that are never used and drop them. In PostgreSQL:

SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan AS times_used FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

In MySQL:

SELECT * FROM sys.schema_unused_indexes;

7. Index Selectivity Is More Important Than Index Count

An index is most valuable when it filters down to a small fraction of rows. A query returning 1% of a table benefits enormously. A query returning 80% does not — the database will likely scan the table instead.

🚀 A Realistic Laravel Optimization Scenario

Consider an e-commerce app with a slow "recent orders for user" page:

// Slow query $orders = Order::where('user_id', $user->id) ->orderByDesc('created_at') ->take(20) ->get();

On a table with 5 million orders, this query could take seconds. Add a composite index:

Schema::table('orders', function (Blueprint $table) { $table->index(['user_id', 'created_at']); });

Now the same query runs in single-digit milliseconds because the index provides both the filter and the sort order.

📊 Index Cheat Sheet

  • Foreign keys → always index.
  • Frequently filtered columns → index if selective.
  • Sorting columns in ORDER BY → index if used often.
  • Low-selectivity columns (booleans, statuses with few values) → usually avoid.
  • Composite index order → most selective / most used columns first, following the leftmost prefix rule.
  • Regularly audit → drop unused indexes; they cost more than they give.

💡 Practical Tips

  1. Start with foreign keys. They are the highest-ROI indexes you'll ever add.
  2. Measure before and after. Use EXPLAIN ANALYZE to confirm improvement.
  3. Test with production-sized data. A 1,000-row test table will never reveal an index problem.
  4. Watch the write path. If inserts slow down, one of your indexes may be the culprit.
  5. Prefer fewer, well-chosen composite indexes over many single-column ones.
  6. Don't index on a hunch. Index what the query planner actually needs.

🔗 Conclusion

Indexes are the silent engine of high-performance applications. They turn crawling queries into instant lookups, protect your database from runaway load, and quietly support every fast page your users ever see.

But they're not free. Every index is a trade: read speed in exchange for write cost and disk space. Add them deliberately, verify they're used, and clean up the ones that aren't. Master this discipline, and your application will scale — elegantly, invisibly, and at speed.

Technologies & topics

Article tags

No articles match these filters.

Have a project or an idea to discuss?

Let's talk ↗