MySQL Indexing for Busy Laravel Apps: Reading EXPLAIN Like a Pro

When a Laravel page gets slow, the first instinct is often a bigger server. In my experience the real cause is usually a query scanning far more rows than it needs to. An index is like a library's card catalogue: instead of walking every shelf, you go straight to the right drawer.

Start with the slow query, not a guess

Find the queries that actually hurt before adding indexes.

• Enable the MySQL slow query log in staging and production
• Use Laravel Telescope or Debugbar locally to see queries per request
• Look for the same query repeated many times — that's an N+1 problem, which eager loading fixes, not an index

Reading EXPLAIN

Prefix the query with EXPLAIN and focus on a few columns.

1. 01type — ALL means a full table scan. You want ref, range or const
2. 02key — the index MySQL actually chose. NULL means none
3. 03rows — the estimated rows examined. Compare it with the rows you actually return
4. 04Extra — watch for Using filesort and Using temporary on large tables

If EXPLAIN says 200,000 rows to return 20, you have found your problem.

Composite indexes: column order matters

A composite index works left to right. For a query like "orders for this customer with this status, newest first", an index on (customer_id, status, created_at) serves the filter and the sort in one go.

The rules of thumb

1. 01Put equality filters first (customer_id = ?, status = ?)
2. 02Put the range or sort column last (created_at)
3. 03Don't create separate single-column indexes and hope MySQL combines them
4. 04In multi-tenant apps, start almost every index with tenant_id

Habits that defeat indexes

Some query patterns stop MySQL from using an index even when one exists.

• Wrapping the column in a function: WHERE DATE(created_at) = ? — use a range instead
• Leading wildcards: LIKE '%term' — consider full-text search
• Comparing a string column to a number, which forces a type conversion
• Selecting * when a covering index could answer the query alone

Indexes aren't free

Every index slows down inserts and updates and takes disk space. Review indexes periodically, drop ones that are never used, and avoid indexing low-cardinality columns on their own — a boolean flag rarely helps by itself.

Make it part of the workflow

Add the index in the same pull request as the query that needs it, and paste the EXPLAIN output into the PR description. Reviewers can see the plan, and the next developer understands why the index exists. Performance then becomes a habit of the team rather than an emergency.