Bug: getCompiledSelect() does not clear $binds, so a reused builder gets slower on every call #10691

PHP Version

8.5

CodeIgniter4 Version

4.7.3

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

cli

Environment

production

Database

MySQL 8.4 (MySQLi driver)

What happened?

BaseBuilder::getCompiledSelect($reset = true) resets the select parts via resetSelect(), but it never clears $this->binds. Only get() does that (// Clear our binds so we don't eat up memory).

When the same builder instance is reused, for example the shared builder returned by Model::builder(), every call adds new binds (queue.1, queue.2, …) and keeps all the old ones. compileFinalQuery() → Query::compileNamedBinds() then escapes every stored bind and adds it to the strtr() map on each call, even though only the newest placeholders appear in the SQL. The SQL stays correct, but the cost of each call grows linearly with the number of earlier calls, so a loop of calls slows down quadratically.

We found this in a long-running codeigniter4/queue worker, which calls getCompiledSelect() on the model's shared builder on every poll. After about 28 hours (about 21k polls) the idle worker was using 20–60% of a CPU core while the queue was empty, and each poll spent about 1 second compiling the query.

Steps to Reproduce

$builder = db_connect()->table('queue_jobs');
$binds   = new ReflectionProperty(\CodeIgniter\Database\BaseBuilder::class, 'binds');

for ($i = 1; $i <= 3; $i++) {
    $builder->where('queue', 'emails')->where('status', 0)->limit(1)->getCompiledSelect();
    echo json_encode(array_keys($binds->getValue($builder))), PHP_EOL;
}

Output:

["queue","status"]
["queue","status","queue.1","status.1"]
["queue","status","queue.1","status.1","queue.2","status.2"]

The cost per call grows with the number of earlier calls on the same builder:

Expected Output

With $reset = true, getCompiledSelect() should leave the builder in the same state as get() does, including clearing $this->binds. After each call above, only ["queue","status"] should remain, and the time per call should stay flat.

Anything else?

A possible fix in BaseBuilder::getCompiledSelect(): compile the final query first, then reset the select parts and clear the binds when $reset is true:

public function getCompiledSelect(bool $reset = true): string
{
    $select = $this->compileFinalQuery($this->compileSelect());

    if ($reset) {
        $this->resetSelect();
        $this->binds = [];
    }

    return $select;
}

The binds have to be cleared after compileFinalQuery(), because it reads them. getCompiledInsert(), getCompiledUpdate() and getCompiledDelete() may be worth checking for the same pattern.