Speed up the CakePHP 5 ORM: fix N+1 queries and heavy finds

Slow CakePHP 5 pages are rarely the ORM’s fault alone; they come from a handful of query patterns: one query per row in a loop (N+1), loading every column of every association, hydrating thousands of entities you only read, and counting related rows on each request. Each has a direct fix in the ORM, and CakePHP 5.4 added two more: subquery eager loading by default and unhydratedFind().

This guide shows how to measure first, then walks through the fixes in the order they usually pay off. Everything targets CakePHP 5.4 (5.4.3 was released on 3 October 2026).

Measure before you change anything

Guessing wastes time. You need the number of queries per request and the slowest statements.

• DebugKit. Installed with the skeleton for development; its SQL panel lists every query a request ran, with timings. A page that ran 120 queries for 20 rows has an N+1 problem.
• Query logging. Set 'log' => true on a datasource and configure a logger for the queriesLog scope. Queries are logged at debug level.
• debug($query). Shows the generated SQL and bound parameters without running it.

// config/app_local.php (development only)
'Datasources' => [
    'default' => [
        // host, username, password, database ...
        'log' => true,
    ],
],
'Log' => [
    'queries' => [
        'className' => 'File',
        'path' => LOGS,
        'file' => 'queries',
        'scopes' => ['queriesLog'],
    ],
],

If query logs might be shipped to a log service, CakePHP 5.4’s LoggedQuery::setRedactor() lets you scrub sensitive parameter values before they are written. Do not leave query logging on in production without it.

Fix N+1 queries with contain()

CakePHP does not lazy-load associations by default, so N+1 problems usually come from code that calls get() or find() inside a loop, often in a template or helper:

// N+1: one query for articles, then one per article for its author
$articles = $this->Articles->find()->limit(20)->all();
foreach ($articles as $article) {
    $author = $this->fetchTable('Users')->get($article->user_id);
}

Declare the association once in ArticlesTable::initialize() ($this->belongsTo('Users'), $this->hasMany('Comments')) and eager-load it:

$articles = $this->Articles->find()
    ->contain(['Users', 'Comments' => ['Users']])
    ->limit(20)
    ->all();

That is now a fixed number of queries no matter how many rows are returned: BelongsTo and HasOne associations are joined into the main query, while HasMany and BelongsToMany are fetched in one extra query each. If you already have a result set and discover you need more data, $this->Articles->loadInto($articles, ['Tags']) eager-loads it in one go instead of per entity.

Select only the columns you use

contain() loads every column of every association by default. On wide tables (long body text, JSON blobs) that dominates transfer and hydration time. Restrict both the main query and the associations:

use Cake\ORM\Query\SelectQuery;

$articles = $this->Articles->find()
    ->select(['Articles.id', 'Articles.title', 'Articles.user_id', 'Articles.created'])
    ->contain([
        'Users' => fn (SelectQuery $q) => $q->select(['Users.id', 'Users.name']),
        'Comments' => fn (SelectQuery $q) => $q
            ->select(['Comments.id', 'Comments.article_id', 'Comments.body'])
            ->where(['Comments.approved' => true]),
    ])
    ->all();

The most common mistake here is leaving out the foreign key. The book warns that if Articles.user_id or Comments.article_id is not selected, the ORM cannot stitch the associated rows back together and they silently disappear from the results.

Filter on associations with matching() or joins, not contain()

Conditions inside contain() only filter the associated records; they never remove parent rows. To find “articles that have an approved comment by user 5”, use matching() or, when you do not need the associated data, innerJoinWith():

$articles = $this->Articles->find()
    ->innerJoinWith('Comments', fn (SelectQuery $q) => $q->where([
        'Comments.user_id' => 5,
        'Comments.approved' => true,
    ]))
    ->distinct(['Articles.id'])
    ->all();

distinct() matters: a join returns one row per matching comment, so without it an article with three matching comments appears three times.

Know the 5.4 eager-loading change

CakePHP 5.4 changed the default strategy for HasMany and BelongsToMany from select to subquery. With select, the ORM sends the list of parent IDs as bound parameters in an IN (...) clause; with subquery, it reuses the parent query as a subquery. The book says this improves performance when the number of parent keys is large, and it avoids hitting bound-parameter limits on databases such as SQL Server.

If an upgraded page got slower, for example because the parent query is a heavy report the database now evaluates twice, opt back in per association with 'strategy' => 'select' in the association options or inside contain(). Measure both on production-sized data; neither is universally faster.

Skip entity hydration for read-only data

Building entity objects costs CPU and memory. For exports, API lists and reports where you never call save(), return arrays. In 5.4, use unhydratedFind(); the older disableHydration() is deprecated and will be removed in 6.0.

$rows = $this->Orders->unhydratedFind()
    ->select(['id', 'total', 'created'])
    ->where(['Orders.created >=' => new \Cake\I18n\Date('-30 days')])
    ->orderBy(['Orders.created' => 'DESC'])
    ->all();

It accepts the same finder names and options as find(), and its static type tells PHPStan or Psalm that results are arrays. Remember that arrays skip entity features: no virtual fields, no accessors and no $_hidden filtering, so select columns carefully before sending them to a client.

Stop counting on every page view

Listing articles with a comment count often means a COUNT() per row or a grouped subquery on each request. If the count is read far more than it changes, store it with the CounterCache behavior. Add an integer comment_count column to articles, then attach the behavior on the child table:

// src/Model/Table/CommentsTable.php
public function initialize(array $config): void
{
    parent::initialize($config);
    $this->belongsTo('Articles');
    $this->addBehavior('CounterCache', [
        'Articles' => ['comment_count'],
    ]);
}

The counter updates whenever a comment entity is saved or deleted. It is not updated by bulk updateAll() or deleteAll() calls, so recalculate after bulk operations.

Cache what rarely changes

For queries whose results change slowly, such as a navigation menu or a “popular this week” list, let the query cache itself:

$popular = $this->Articles->find('published')
    ->select(['id', 'title', 'slug'])
    ->orderBy(['Articles.view_count' => 'DESC'])
    ->limit(10)
    ->cache('popular_articles', 'long')
    ->all();

The second argument is a cache configuration name. Invalidate it from afterSave or with a short duration; serving stale data is the price of query caching.

Indexes still decide most of it

No ORM technique rescues a missing index. Make sure every foreign key used in contain() or joins is indexed, along with columns in frequent where() and orderBy() clauses. Copy a slow statement from DebugKit and run EXPLAIN on it:

EXPLAIN SELECT Comments.id, Comments.article_id, Comments.body
FROM comments Comments
WHERE Comments.approved = 1 AND Comments.article_id IN (1, 2, 3);

A type of ALL on a large table means a full scan. Add the index in a migration, not by hand, so every environment gets it.

Quick checklist

What to do next

1. Open your three busiest pages with DebugKit and write down the query count for each.
2. Fix N+1 loops first; they give the biggest wins for the least risk.
3. Move read-only exports and API lists to unhydratedFind() with explicit select() lists.