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

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

Single-column indexes are the first thing every developer reaches for. They solve obvious problems, but once your tables grow past a few million rows you start hitting walls that ->index('user_id') alone cannot break through. This article focuses on three underused MySQL 8 index features that pair naturally with Laravel migrations and Eloquent.

1. Composite Indexes and Column Order

MySQL can only use a composite index from the leftmost prefix. If your query filters on status and created_at, the index (status, created_at) serves both a WHERE status = ? and a WHERE status = ? AND created_at > ?. Reversing the order makes the index useless for status-only lookups.

// database/migrations/2024_06_01_create_orders_table.php
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->string('status', 20);
    $table->timestamp('created_at')->nullable();

    // Good: filters narrow by status first, then sort/range on created_at
    $table->index(['status', 'created_at'], 'idx_status_created');
});

The corresponding Eloquent scope:

// Both columns hit the index; ORDER BY created_at DESC is also covered
Order::where('status', 'pending')
    ->where('created_at', '>=', now()->subDays(7))
    ->orderByDesc('created_at')
    ->get();

Run EXPLAIN to confirm key shows idx_status_created and Extra does not say Using filesort.

EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at >= NOW() - INTERVAL 7 DAY
ORDER BY created_at DESC;

2. Prefix Indexes on Long String Columns

Indexing a full TEXT or VARCHAR(500) column wastes buffer pool space and slows writes. A prefix index on the first n characters is often enough for high selectivity.

Schema::table('articles', function (Blueprint $table) {
    // Index only the first 80 characters of the slug
    $table->index(DB::raw('slug(80)'), 'idx_slug_prefix');
});

Choose the prefix length by measuring selectivity:

SELECT
    COUNT(DISTINCT LEFT(slug, 40)) / COUNT(*) AS sel_40,
    COUNT(DISTINCT LEFT(slug, 80)) / COUNT(*) AS sel_80,
    COUNT(DISTINCT slug)           / COUNT(*) AS sel_full
FROM articles;

Stop increasing the prefix once selectivity plateaus. A prefix index cannot satisfy ORDER BY or cover a range scan, so use it only for equality lookups.

3. Invisible Indexes (MySQL 8.0+)

Dropping an index to test whether it matters is destructive. MySQL 8 lets you make an index invisible — the optimizer ignores it, but the engine still maintains it. You can flip it back instantly.

// Make an existing index invisible via a raw statement in a migration
DB::statement('ALTER TABLE orders ALTER INDEX idx_old_status INVISIBLE');

Monitor your slow query log or Percona Monitoring for a few hours. If nothing degrades:

// Safe to drop
Schema::table('orders', function (Blueprint $table) {
    $table->dropIndex('idx_old_status');
});

To restore visibility without a full rebuild:

DB::statement('ALTER TABLE orders ALTER INDEX idx_old_status VISIBLE');

This is the safest way to audit index bloat on a live production table.

Putting It Together: A Migration Review Checklist

Before deploying any migration that adds or removes an index:

1. Run EXPLAIN on the top five queries that touch the table.
2. Check rows and Extra columns — Using filesort or Using temporary are red flags.
3. Validate composite column order matches your most selective filter first.
4. Use prefix indexes only on equality-lookup columns; never for range or sort.
5. Prefer invisible indexes over blind drops on tables with >1 M rows.

Key Takeaways

• Leftmost prefix rule: composite index column order must mirror your WHERE clause filter order.
• Prefix indexes reduce index size on long strings; measure selectivity before choosing length.
• Invisible indexes in MySQL 8 let you safely test index removal without a destructive drop.
• Always validate with EXPLAIN — never assume an index is being used.
• Laravel migrations support raw expressions for prefix indexes via DB::raw().