Database Performance: Partial Indexes, Covering Indexes, and Query Plan Analysis in Laravel

Why Generic Indexes Leave Performance on the Table

Most Laravel developers reach for $table->index('status') and call it done. That works — until your orders table has 50 million rows, 48 million of which have status = 'completed'. The index is enormous, the selectivity is terrible, and Postgres or MySQL may ignore it entirely.

Two underused tools fix this: partial indexes (index only the rows you actually query) and covering indexes (include all columns a query needs so it never touches the heap).

Partial Indexes: Index Only What You Query

A partial index carries a WHERE clause. Only rows satisfying that clause are indexed, making the structure smaller and faster.

Creating a Partial Index in a Migration

// database/migrations/2024_06_01_000001_add_partial_index_to_orders.php

public function up(): void
{
    // Raw DDL — Laravel's Schema builder has no first-class partial index API
    DB::statement(
        'CREATE INDEX idx_orders_pending_created
         ON orders (created_at DESC)
         WHERE status = \'pending\''
    );
}

public function down(): void
{
    DB::statement('DROP INDEX IF EXISTS idx_orders_pending_created');
}

Now a query like:

Order::where('status', 'pending')
    ->orderByDesc('created_at')
    ->limit(100)
    ->get();

…hits an index that contains only pending rows. If pending orders are 2 % of the table, the index is 50× smaller than a full-column index.

MySQL note: MySQL 8.0+ supports functional indexes but not WHERE-clause partial indexes. Use a generated column + index as a workaround, or switch to PostgreSQL for this pattern.

Covering Indexes: Eliminate Heap Fetches

A covering index stores every column a query needs, so the engine returns results directly from the index without reading the table rows (an "index-only scan" in Postgres, a "covering index" in MySQL).

DB::statement(
    'CREATE INDEX idx_invoices_covering
     ON invoices (user_id, status)
     INCLUDE (total_cents, due_at)'
    // INCLUDE is PostgreSQL 11+; MySQL uses a composite index instead
);

For MySQL, list all needed columns in the index itself:

$table->index(['user_id', 'status', 'total_cents', 'due_at'], 'idx_invoices_covering');

The query below now never touches the invoices heap:

Invoice::where('user_id', $userId)
    ->where('status', 'unpaid')
    ->select(['total_cents', 'due_at'])
    ->get();

Reading the Query Plan

Always verify with EXPLAIN (ANALYZE, BUFFERS) (Postgres) or EXPLAIN FORMAT=JSON (MySQL).

// Quick helper — never run in production without guards
$sql = Invoice::where('user_id', 1)->where('status', 'unpaid')
    ->select(['total_cents', 'due_at'])
    ->toRawSql();

$plan = DB::select('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' . $sql);
dd($plan);

What to Look For

| Node type | Meaning | |---|---| | Index Only Scan | Covering index hit — ideal | | Index Scan | Index used, heap fetched per row | | Bitmap Heap Scan | Batch heap fetch — OK for moderate row counts | | Seq Scan | Full table scan — investigate |

If you see Seq Scan after adding an index, check: is the planner's row-count estimate accurate? Run ANALYZE orders; to refresh statistics.

Keeping Migrations Reversible and Documented

public function up(): void
{
    // Document intent inline — future engineers will thank you
    // Partial index: only active subscriptions need fast lookup by next_billing_at
    DB::statement(
        'CREATE INDEX idx_subscriptions_active_billing
         ON subscriptions (next_billing_at)
         WHERE cancelled_at IS NULL'
    );
}

public function down(): void
{
    DB::statement('DROP INDEX IF EXISTS idx_subscriptions_active_billing');
}

Keep raw DDL migrations in a dedicated database/migrations/indexes/ folder so they're easy to audit separately from schema changes.

Key Takeaways

• Partial indexes are ideal for low-selectivity columns where you always filter to a small subset (status flags, soft-delete columns, boolean fields).
• Covering indexes eliminate heap fetches; use INCLUDE on Postgres 11+ or composite indexes on MySQL.
• Always verify with EXPLAIN (ANALYZE, BUFFERS) — never assume an index is used.
• Run ANALYZE after bulk inserts to keep planner statistics fresh.
• Document raw DDL migrations with inline comments; group them in a dedicated folder.