Laravel / Advanced / Lesson 15

Query performance and indexes

Investigate query count, returned data, and access paths using a declared workload instead of treating an index or eager loading as a universal speed guarantee.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Recognize N+1 access and preserve relation keys during projection.
Design candidate composite indexes from actual predicates and ordering.
Separate query-shape tests from database execution-plan evidence.

Define the request and its data model

The synthetic fixture has posts with id, tenant_id, author_id, status, and title, plus users with id and name. Post has an author belongsTo relationship. An already-authorized tenant requests at most fifty published posts ordered by id, with author names. Measure total query count, database time, rows examined, transferred columns, and rendering behavior rather than a single average latency. A slow endpoint may spend time outside SQL, and a fast query repeated once per rendered row can still dominate. No configured database, migrations, or execution plans are touched in this lesson.

Batch related access instead of repeating it

Lazy relationship access inside a loop can create N+1 behavior. The offline PHP model compares one lookup request per post with one batch request for the distinct author identifiers; it demonstrates a data-access strategy, not actual SQL counts. Laravel eager loading can retrieve related rows in a batch, but projections must retain parent primary keys and relationship foreign keys. Removing author_id while selecting only title can prevent the relation from matching correctly. Test the actual relationship and any global scopes because polymorphic relations, additional hooks, or nested access can change the real query budget.

php Offline model of individual versus batch requests

<?php
declare(strict_types=1);
$posts = [
    ["id" => 1, "author_id" => 7],
    ["id" => 2, "author_id" => 7],
    ["id" => 3, "author_id" => 9],
];
$authorIds = array_values(array_unique(array_column($posts, "author_id")));
echo "individual=", count($posts), " batch=", (count($authorIds) > 0 ? 1 : 0), PHP_EOL;
echo json_encode($authorIds, JSON_THROW_ON_ERROR), PHP_EOL;

Expected output

individual=3 batch=1
[7,9]

Three posts would request three individual lookups; the unique identifiers 7 and 9 can be requested together. The output does not claim measured database behavior.

Preserve keys while bounding the returned set

The contextual Eloquent query includes the keys needed to associate authors and limits the requested parent rows. The validated tenant comes from authorization context, not an arbitrary trusted request field. Its author relationship and schema are prerequisites explicitly declared above. This fragment is not executed and provides no fabricated rows or timing. For the ordinary nonempty belongsTo fixture, eager loading is intended to replace repeated relation fetches with one related lookup; verify that expectation in an isolated database test. A limit controls returned parent rows but does not by itself guarantee an efficient access path.

php Contextual tenant-scoped eager-loading query

<?php
use App\Models\Post;

$posts = Post::query()
    ->where("tenant_id", $authorizedTenantId)
    ->where("status", "published")
    ->with("author:id,name")
    ->orderBy("id")
    ->limit(50)
    ->get(["id", "author_id", "title"]);

Expected observation in the declared isolated fixture: parent records retain id and author_id, and author access uses eager-loaded data. Actual SQL counts and plans depend on the complete model configuration.

Derive an index hypothesis from predicates and order

A candidate index on tenant_id, status, id may support this equality-filtered, ordered access pattern. The migration fragment records that hypothesis, not proof of optimality. Engine, collation, data distribution, selectivity, existing indexes, and write workload affect the result. Inspect an execution plan and representative timings only in an authorized isolated database fixture before accepting a change. Indexes consume storage and make some writes more expensive. Avoid adding every filter permutation. Offset pagination can become expensive at deep pages; cursor pagination requires a suitable stable ordering and its own documented constraints.

php Unexecuted candidate-index migration fragment

<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

Schema::table("posts", function (Blueprint $table): void {
    $table->index(["tenant_id", "status", "id"], "posts_tenant_status_id_idx");
});

The fragment would declare a composite index if placed in an authorized migration for the stated table. It is not run here and does not guarantee index selection or a speedup.

Verify the complete request, not only one statement

Protect query shape with bounded query-count and relation-access tests in the synthetic fixture, and protect permissions with tenant-isolation tests. Use query listeners or cumulative-query-time monitoring cautiously, avoiding raw sensitive bindings in logs. Large exports may need chunkById or lazyById rather than loading everything, especially when updates change scanned rows. Memory, ordering, transaction scope, and concurrent writes remain part of that design. Recheck response data and write cost after an index change. Never report an EXPLAIN result or benchmark that was not actually executed; keep unmeasured recommendations labeled as hypotheses.

Common errors and fixes

Rendering each post adds another author query.

Why it happens: The view triggers lazy relationship loading in a loop.
How to fix it: Eager load the needed relation and preserve matching keys.
Prevent it next time: Test query growth as fixture row count increases.

A projected query loses author data.

Why it happens: The relation foreign key was omitted from the projection.
How to fix it: Include the parent and related matching keys.
Prevent it next time: Review eager-loading projections with relation tests.

An extra index slows writes without helping the request.

Why it happens: The candidate was accepted without workload and plan evidence.
How to fix it: Measure the authorized fixture and compare read/write costs.
Prevent it next time: Treat each index as a tested hypothesis, not a universal rule.

Check your understanding

1. The offline fixture receives a fourth post by author 7. Give the two request-model counts and explain why they are not literal Eloquent query totals.

Show worked solution

Individual requests become four, while the model still needs one batch for author identifiers 7 and 9. The model excludes the parent query, connection behavior, scopes, hooks, and nested relations. A real test must count the complete request under its declared schema and model configuration instead of copying these simplified counts as a universal assertion.

2. A new filter adds category_id. Should its index always replace the candidate with tenant_id,status,category_id,id? Describe the evidence needed.