SQL Indexes That Make Queries Fast: A Practical Guide With Laravel

Almost every slow endpoint I get asked about has the same cause, a table scan on a column with no index. Laravel makes that easy to write and easy to forget, because nobody notices until the table passes a hundred thousand rows.

What an index actually does

MySQL and PostgreSQL store indexes in a B-tree, whose keys stay sorted and whose leaves sit next to each other in storage. That one property explains the behaviour: finding a single key touches a few pages instead of every row, and two conditions on one column are served by walking the tree once from the start of the range to its end.

The other half matters too: every insert and update must be written into every index, so an index is a tax you pay on writes to buy fast reads.

Reading EXPLAIN without guessing

Run EXPLAIN on the real query with real parameters and read it top to bottom, looking at the access type, the estimated rows, the filtered percentage and the extra column.

• A sequential scan on a large table means no usable index. Check the column and the value type.
• An index scan with a huge row estimate means the index is not selective enough for the optimizer.
• Rows removed by a filter usually means a wildcard or a non-sargable expression.
• A temporary table or filesort means ORDER BY could not be served from index order, so fix the index first.
• A dependent subquery often points at an N plus one in Eloquent, one query per parent row.

Composite index column order

A multi-column index is not several indexes, it is one sorted list, and the column order decides which queries it serves: equality conditions first, then one range column, then columns used only for sorting or covering.

• Filtering on a tenant column and a created_at range uses an index on both, because the range stops at the first ranged column.
• Filtering on a user column and sorting by created_at descending also works, since the engine walks the index backwards.
• Prepending an equality column often makes the old single-column index redundant.
• Test the direction you query, because mixing ascending and descending changes what the engine can do.

Covering indexes

A covering index holds every column the query needs, so the database answers from the index and never visits the table. Add those columns to an existing index rather than creating another, and look for the word covering in the EXPLAIN extra column. The catch is write amplification, so this belongs on your few hot queries.

Foreign keys and the cost of over-indexing

Foreign key columns need indexes for a reason unrelated to convenience: when a row is deleted, the engine must prove no child rows point at it, and without an index that check becomes a table scan while holding locks.

• Index every foreign key column and every join column, this is the cheapest win in the system.
• Index the columns you filter, sort and join, but not columns you only select.
• In Laravel put indexes in migrations, not in a manual console session.
• Log every query in tests, which catches N plus one regressions before production.

Key takeaways

• An index is a B-tree, so sorting and range scans get cheap while every write pays.
• Read access type, row estimate and extra in EXPLAIN before touching code.
• Equality columns first, then one range column, then sorting columns.
• Covering indexes answer without touching the table, so keep them on hot paths.
• Index every foreign key and keep the total small enough that writes stay fast.

FAQ

Q: How do I find out which indexes are actually used in production?

A: Enable the performance schema or your provider slow query log, then compare the important queries with your index definitions. In MySQL it gives a per-table counter for unused indexes, and dropping them after a representative traffic window is the safest way to remove an index that only slows writes.

Q: Is it bad to index every column that appears in a WHERE clause?

A: It becomes bad as soon as write volume matters, because every index is updated on each insert and update. Ask instead which queries are frequent or slow, since a single well-ordered composite index usually serves several conditions at once.