Indexing Strategy and Query Optimization

Indexing Is Not "Add Index to Everything"

Indexes speed up reads but slow down writes (every INSERT, UPDATE, and DELETE must also update the index). The skill is knowing which columns to index and how to structure those indexes.

When to Add an Index

Index columns that appear in:

• WHERE clauses — WHERE status = 'active' benefits from an index on status.
• JOIN conditions — Foreign keys used in joins should always be indexed.
• ORDER BY — Sorting without an index forces a "filesort" (slow).
• Unique constraints — unique() in migrations automatically creates an index.

Don't index columns with very low cardinality (like a boolean with only two values) unless combined with other columns. The database won't use an index that eliminates less than ~10-20% of rows.

Composite Indexes

A composite index covers multiple columns. The order of columns matters enormously due to the leftmost prefix rule.

Given an index on (course, is_published, sort_order):

• A query filtering on course alone — uses the index.
• A query filtering on course AND is_published — uses the index.
• A query filtering on all three — uses the index.
• A query filtering on is_published alone — does NOT use the index (skipped the first column).

In your Laravel migration:

$table->index(['course', 'is_published', 'sort_order']);

Design composite indexes with the most selective column first (the one that eliminates the most rows), then progressively less selective columns.

The N+1 Problem

This is the single most common performance issue in Laravel applications. Consider:

$posts = Post::all(); // 1 query: SELECT * FROM posts

foreach ($posts as $post) {
    echo $post->author->name; // N queries: SELECT * FROM users WHERE id = ?
}

For 100 posts, this runs 101 queries (1 for posts + 100 for authors). With 1,000 posts, it's 1,001 queries.

The fix is eager loading:

$posts = Post::with('author')->get(); // 2 queries total
// SELECT * FROM posts
// SELECT * FROM users WHERE id IN (1, 2, 3, ...)

Now it's always exactly 2 queries regardless of how many posts exist. Laravel batches all the author IDs into a single WHERE IN query.

You can also use $with on the model to always eager load a relationship:

class Post extends Model
{
    protected $with = ['author'];
}

And in Laravel 11+, you can enable strict mode to catch N+1 problems during development:

Model::preventLazyLoading(!app()->isProduction());

Key Takeaways

• Index columns used in WHERE, JOIN, and ORDER BY. Don't index everything — it slows writes.
• Composite indexes follow the leftmost prefix rule. Column order matters.
• The N+1 problem is the #1 Laravel performance issue. Fix it with with() eager loading.
• Use preventLazyLoading() in development to catch N+1 problems automatically.