An index is a data structure with a purpose, not a checkbox in a Laravel migration. The useful question is never “Is this column indexed?” but “Can MySQL use this index for the query shape and ordering we actually run?” Good MySQL indexing for Laravel begins with evidence.

Capture the real query

Start from a slow page or endpoint. Capture SQL with bindings, execution frequency, rows returned, and data volume. A query taking 200 ms once a day is different from one taking 20 ms ten thousand times a minute. Optimise for user and system impact.

Run EXPLAIN on representative production-like data. Look at the chosen key, estimated rows, access type, and extra work such as filesort or temporary tables. Estimates are not perfect, but they replace blind index suggestions with a plan.

Understand composite index order

Suppose the common query is:

Order::query()
    ->where('tenant_id', $tenantId)
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->limit(50)
    ->get();

An index on (tenant_id, status, created_at) can support filtering and ordering. Three independent indexes do not necessarily provide the same result. The leftmost prefix matters: an index beginning with tenant and status cannot efficiently answer every query on created_at alone.

Selectivity affects usefulness

A boolean column with half true and half false may not narrow enough rows to justify an index by itself. Combined with tenant or date it may become useful. MySQL’s optimizer chooses based on expected cost, so an existing index can still be ignored.

Avoid expressions that hide indexed values

whereDate('created_at', ...) can apply a function that prevents a normal index range scan. Prefer explicit start and end timestamps. Leading-wildcard searches such as LIKE '%term' cannot use a standard B-tree efficiently. Type mismatches between joined columns also cause surprising plans.

Covering indexes can remove table reads

If an index contains every column needed by a frequent narrow query, MySQL may answer from the index alone. This can help high-volume lookups, but wide indexes consume memory and slow writes. Select only needed columns in Eloquent rather than turning every index into a copy of the table.

Pagination changes the query cost

Offset 100,000 still asks the database to walk past many rows. Cursor pagination using the ordered key keeps work bounded for large feeds. Use a deterministic order, commonly (created_at, id), and align the index with it.

Every index has a write cost

Insert, update, storage, backup, and buffer-pool pressure increase with each index. Find duplicates and indexes whose leading columns are already covered by a better composite index. Remove only after checking real usage and deployment risk.

Laravel-specific review points

  • Foreign keys used for joins and relationship lookups
  • Tenant keys included in constraints and common query indexes
  • Soft-delete columns in frequent filtered queries
  • Polymorphic type and ID pairs indexed together
  • Scheduled reports isolated from interactive traffic

Measure after the migration

Compare execution time, examined rows, CPU, and p95 endpoint latency. Adding a production index to a large table also needs an online DDL and rollout plan; the correct index can still cause an incident if built carelessly.

If slow database work appears across the product, a Laravel performance audit can rank queries by real impact. Caching should follow query correction, as explained in the Laravel Redis cache strategy.

Frequently asked questions

Should every foreign key have an index?

Frequently queried and joined foreign keys usually should. Confirm existing indexes and query patterns rather than adding duplicates blindly.

Why is MySQL ignoring my index?

Low selectivity, functions on the column, type mismatches, leading wildcards, or a composite index with the wrong leading columns are common causes.