MySQL Indexing Mistakes That Slow Down Laravel Apps

You have removed the N+1s, the page still takes two seconds, and the query log shows one statement taking most of it. That is an index problem, and it does not appear until the table has real data in it.

Find the slow query first

Do not optimise by intuition. Ask MySQL what it is doing:

EXPLAIN SELECT * FROM projects WHERE category = 'web' ORDER BY created_at DESC LIMIT 15;

Two columns matter:

• type — ALL means a full table scan. Anything else is better.
• rows — roughly how many rows are examined. If that is near the table size to return 15, there is no useful index.

Mistake 1: no index on what you filter by

Foreign keys usually get one from the migration. Status and category columns rarely do:

Schema::table('projects', function (Blueprint $table) {
    $table->index('category');
    $table->index('is_active');
});

Index the columns that appear in where, order by and join — not every column.

Mistake 2: an index the query cannot use

An index on created_at is useless here:

WHERE DATE(created_at) = '2026-08-15'

Wrapping the column in a function forces a scan of every row. Compare against a range instead:

WHERE created_at >= '2026-08-15 00:00:00'
  AND created_at <  '2026-08-16 00:00:00'

In Eloquent:

// scans everything
Project::whereDate('created_at', $date)->get();

// uses the index
Project::whereBetween('created_at', [
    $date->copy()->startOfDay(),
    $date->copy()->endOfDay(),
])->get();

The same applies to LIKE '%term%' — a leading wildcard cannot use an index. For real text search, use a FULLTEXT index or a search engine.

Mistake 3: column order in a composite index

A composite index works left to right. Given:

$table->index(['is_active', 'category']);

MySQL can use it for is_active, or for is_active AND category — but not for category alone. Put the column used in every query first, and the most selective one before the less selective.

Mistake 4: indexing everything

Every index has to be updated on write and takes disk. A table with fifteen indexes has slow inserts and a confused planner. Find the ones nobody uses:

SELECT * FROM sys.schema_unused_indexes;

Mistake 5: ORDER BY forcing a filesort

Filtering on one column and sorting by another, with separate indexes on each, still sorts in memory. A composite covering both lets the index do it:

$table->index(['category', 'created_at']);

Now WHERE category = ? ORDER BY created_at DESC is served by one index, and Using filesort disappears from EXPLAIN.

A practical order of work

1. Log queries and find the slow one — do not guess
2. Run EXPLAIN; look at type and rows
3. Add an index for the where and order columns
4. Re-run EXPLAIN and confirm it changed
5. Measure again with production-sized data

Step five matters most. An index that helps with a thousand rows can be irrelevant at a million, and you will not know until you test at that size.

If your query count is still high, indexes are not your problem yet — start with N+1 queries.