CakePHP 5.4.3 may speed up building queries with deeply nested conditions

TL;DR: CakePHP 5.4.3 fixes a bug where compiling a query with deeply nested WHERE conditions got exponentially slower. On MySQL/MariaDB it was a regression in 5.4.0–5.4.2 (or already slow in 5.3 if you enable quoteIdentifiers); on PostgreSQL and SQLite it was already slow in 5.3. Whatever your database, if your app builds conditions by nesting them, upgrading to 5.4.3 alone may make it faster.

Avoid repeated query expression visits #19637

What was fixed

The release notes for CakePHP 5.4.3, released on October 3, 2026, contain this line:

Fixed a performance regression related to duplicate query expression visitor traversals.

It reads like a minor note, but the original Issue #19636 tells a bigger story. In the reporter’s production app, 11 AND-ed search conditions (each an OR of 2–4 comparisons) were nested 19 levels deep, and:

• On 5.3.6, compiling the query took 2 ms
• On 5.4, compiling the same query took 9 seconds
• The page compiled it 4 times (count, fetch, and a belongsToMany eager load with the subquery strategy), so it took 35 seconds to render

That’s just PHP building an SQL string, before anything is sent to the database. Ouch.

Why it was slow

The same expressions were visited over and over

CakePHP’s query builder holds WHERE conditions and the like as a tree of QueryExpression objects. Right before compiling the SQL, the driver walks the whole tree with Query::traverseExpressions() and applies database-specific rewrites (expression translators).

The walk was the problem. QueryExpression::traverse() already recurses into all descendants, yet the callback in Query::_expressionsVisitor() also recursed into each child expression.

// Query::_expressionsVisitor() in 5.4.2 (excerpt)
if ($expression instanceof ExpressionInterface) {
    // traverse() recurses into descendants, and the callback recurses again
    $expression->traverse(fn($exp) => $this->_expressionsVisitor($exp, $callback));
    // (snip)
}

As a result, the number of visits doubles with every level of nesting. Fewer than 100 expressions can end up being visited millions of times.

In 5.4.3, a WeakMap remembers visited expressions, so a single traversal never visits the same expression twice.

Why only MySQL got slower in 5.4

The duplicate traversal code already existed in 5.3. So why is it a “5.4 regression”? Because whether the traversal runs depends on the driver’s configuration and implementation.

There are two paths from Driver::transformQuery() into traverseExpressions().

// Driver::transformQuery() (excerpt)
if ($this->isAutoQuotingEnabled()) {
    // Path 1: IdentifierQuoter::quote() calls traverseExpressions()
    $query = $this->quoter()->quote($query);
}

// (snip)

// Path 2: no traversal when there are no expression translators
$translators = $this->_expressionTranslators();
if (!$translators) {
    return $query;
}

$query->traverseExpressions(function ($expression) use ($translators, $query): void {
    // (snip)

Path 1, automatic identifier quoting, is only enabled when you set 'quoteIdentifiers' => true in your datasource config. The default in cakephp/app’s config/app.php is false, so for most apps only path 2 matters.

Here are the translators per driver used by path 2, in 5.3.7 and 5.4.2:

The MySQL driver got its first translators in 5.4, so the traversal started running there and the slowness surfaced.

Put the other way around, PostgreSQL, SQLite and SQL Server were already slow in 5.3. And since path 1 doesn’t depend on the database, MySQL was also already slow in 5.3 if you enabled quoteIdentifiers.

Measuring it locally

Compiling SQL doesn’t need a database connection, so I set up throwaway projects with just cakephp/database at 5.3.7, 5.4.2 and 5.4.3.

composer require cakephp/database:5.4.2

Each time a search condition is added, the script wraps all previous conditions in a new and().

<?php
require 'vendor/autoload.php';

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;
use Cake\Database\Driver\Postgres;
use Cake\Database\Driver\Sqlite;

// Only compiles SQL, so no database connection is needed
foreach ([Mysql::class, Postgres::class, Sqlite::class] as $driverClass) {
    $connection = new Connection(['driver' => new $driverClass()]);

    foreach ([11, 15, 19] as $depth) {
        $query = $connection->selectQuery('id', 'articles');

        // Stack search conditions by re-wrapping them in AND one at a time
        $conditions = $query->expr(['published' => true]);
        for ($i = 0; $i < $depth; $i++) {
            $conditions = $query->expr()->and([
                $conditions,
                $query->expr()->or(["title$i" => 'foo', "body$i" => 'bar']),
            ]);
        }
        $query->where($conditions);

        $visits = 0;
        $query->traverseExpressions(function () use (&$visits): void {
            $visits++;
        });

        $start = hrtime(true);
        $query->sql();
        $ms = (hrtime(true) - $start) / 1e6;

        printf("%-8s depth %2d: %8d visits, sql() %9.1f ms\n", substr(strrchr($driverClass, '\\'), 1), $depth, $visits, $ms);
    }
}

Here is how long sql() took at 19 levels of nesting, on PHP 8.5 with Xdebug disabled:

The visit count went from 7,340,022 on 5.4.2 to 79 on 5.4.3. At 11 and 15 levels it went from 28,662 to 47 and from 458,742 to 63. On 5.4.3 it only grows with the number of expressions.

With quoteIdentifiers enabled

Here are the results at 19 levels of nesting after changing the driver construction to new $driverClass(['quoteIdentifiers' => true]):

MySQL already took 4.5 seconds on 5.3.7, and 5.4.3 brings it down to 0.3 ms. On 5.4.2 it’s even slower because the tree is traversed by both path 1 and path 2.

With quoteIdentifiers at its default of false, the MySQL driver in 5.3.7 doesn’t traverse at all, so upgrading to 5.4.3 won’t make it faster than 5.3.

Note: I couldn’t measure SQL Server because I don’t have pdo_sqlsrv installed. Since it has translators, it goes through the same code path as PostgreSQL and SQLite.

Will my app get faster?

Honestly, most apps won’t notice a difference.

If you just chain where() calls as usual, the conditions are added flat to the same QueryExpression, so nesting barely grows.

// This doesn't nest (conditions are just added to the AND QueryExpression)
$query = $this->Articles->find()
    ->where(['published' => true])
    ->where(fn(QueryExpression $exp) => $exp->or(['title LIKE' => $q, 'body LIKE' => $q]))
    ->where(['category_id' => $categoryId]);

Measured the same way, chaining where() 11 times took only about 0.6 ms for sql() even on 5.4.2.

Where it matters is when you stack conditions by wrapping all previous conditions in and() / or(), for example when building conditions dynamically from a search form. In the measurements above, 11 levels took about 10 ms, and every 4 more levels made it about 16 times slower.

The quickest way to see whether your query is affected is to count the visits of traverseExpressions() on the version you’re running before upgrading.

// Run this on 5.4.2 or earlier. If the count is orders of magnitude larger
// than the number of expressions, 5.4.3 will make it faster
$visits = 0;
$query->traverseExpressions(function () use (&$visits): void {
    $visits++;
});
debug($visits);

Cake\ORM\Query\SelectQuery, which Table::find() returns, extends Cake\Database\Query\SelectQuery, so you can check ORM queries the same way.

By the way, in the app I’m developing, the whole CI test run got about 5% faster. Yay!

A note for plugin authors

With this fix, Query::_expressionsVisitor() throws a CakeException when called outside of traverseExpressions(). If you extend Query and call _expressionsVisitor() directly, set $this->visitedExpressions = new WeakMap(); before calling it, as the tests in the PR do. Regular application code isn’t affected.

Summary

• CakePHP 5.4.3 fixes query compilation that got exponentially slower with deeply nested conditions
• On MySQL/MariaDB it was a regression in 5.4.0–5.4.2. PostgreSQL, SQLite (and SQL Server) had the same problem since 5.3 or earlier
• If you enable quoteIdentifiers, MySQL/MariaDB also had the same problem since 5.3 or earlier
• If you just chain where() flat, there’s hardly any difference. If you stack conditions by re-wrapping them, it gets dramatically faster

So if you’re a PostgreSQL user, or have quoteIdentifiers enabled, and have wondered why your search page is oddly slow, upgrading to 5.4.3 might speed it up. The latest 5.3 release (5.3.7) doesn’t include this fix, so consider moving to 5.4.

References

• Avoid repeated query expression visits #19637 (GitHub)
• Query::traverseExpressions() is exponential in the nesting depth of QueryExpression #19636 (GitHub)
• CakePHP 5.4.3 release notes (GitHub)