MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes

MySQL Index Strategies for Laravel: Composite, Prefix, and Invisible Indexes

Most Laravel developers add indexes reactively — a slow query appears, ->index() gets added to a migration, and the problem is considered solved. That works until it doesn't. Understanding how MySQL uses indexes lets you make deliberate choices rather than hopeful ones.

Composite Indexes and Column Order

MySQL can only use a composite index from the leftmost prefix. If you index (status, created_at), a query filtering only on created_at will not use that index.

// Migration
$table->index(['status', 'created_at'], 'orders_status_created_at_idx');

This index satisfies:

• WHERE status = 'pending'
• WHERE status = 'pending' AND created_at > ?
• ORDER BY status, created_at (index scan, no filesort)

It does not satisfy WHERE created_at > ? alone. Run EXPLAIN to confirm:

EXPLAIN SELECT id, total
FROM orders
WHERE status = 'pending'
  AND created_at > '2024-01-01'
ORDER BY created_at;

Look for key: orders_status_created_at_idx and Extra: Using index condition — that's the index being used with an ICP (Index Condition Pushdown) optimisation.

Covering Indexes to Eliminate Table Lookups

When every column in a SELECT is present in the index, MySQL reads only the index B-tree and never touches the row data. This is a covering index.

// Covers: SELECT id, status, created_at FROM orders WHERE status = ?
$table->index(['status', 'created_at', 'id'], 'orders_covering_idx');

In EXPLAIN, Extra: Using index (without "condition") confirms a covering scan. For high-read tables with narrow projections this can halve I/O.

Prefix Indexes for Long VARCHAR Columns

Indexing a full TEXT or long VARCHAR column wastes buffer pool space. A prefix index indexes only the first n characters.

$table->index(DB::raw('email(20)'), 'users_email_prefix_idx');

The trade-off: prefix indexes cannot be covering indexes, and MySQL must verify the full value after the index lookup. Use them when cardinality is still high within the prefix length. Avoid them on columns used in ORDER BY — MySQL cannot use a prefix index to satisfy ordering.

Invisible Indexes: Safe Removal Testing

MySQL 8.0+ supports invisible indexes. The index is maintained but the optimiser ignores it, letting you validate that removing an index won't degrade queries before you actually drop it.

// Make an existing index invisible
DB::statement('ALTER TABLE orders ALTER INDEX orders_old_idx INVISIBLE');

Run your workload, monitor slow query logs, then either restore visibility or drop:

// Restore
DB::statement('ALTER TABLE orders ALTER INDEX orders_old_idx VISIBLE');

// Or drop confidently
$table->dropIndex('orders_old_idx');

This is far safer than dropping indexes in production and hoping nothing breaks.

Applying This in Laravel Migrations

public function up(): void
{
    Schema::table('orders', function (Blueprint $table) {
        // Composite for status-filtered, date-sorted queries
        $table->index(['status', 'created_at'], 'orders_status_created_idx');

        // Covering index for dashboard aggregate query
        $table->index(
            ['user_id', 'status', 'total'],
            'orders_user_status_total_idx'
        );
    });
}

For invisible indexes, use DB::statement directly since Blueprint has no native support yet.

Validating With EXPLAIN ANALYZE

MySQL 8.0.18+ supports EXPLAIN ANALYZE, which executes the query and returns actual row counts and timings:

EXPLAIN ANALYZE
SELECT user_id, SUM(total)
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

Compare rows (estimated) vs actual rows — large divergence means stale statistics. Run ANALYZE TABLE orders to refresh them.

Takeaways

• Column order in composite indexes is not arbitrary — leftmost prefix rule governs usability.
• Covering indexes eliminate row lookups and are the highest-impact optimisation for read-heavy queries.
• Prefix indexes save space on long strings but cannot cover or sort; use them carefully.
• Invisible indexes are the safest way to test index removal in production without risk.
• EXPLAIN ANALYZE gives actual execution data; use it, not just EXPLAIN, when diagnosing slow queries.
• Always re-run EXPLAIN after adding an index — the optimiser may still prefer a full scan if selectivity is low.