Avoid executing MySQL queries within loops

One of the most common performance mistakes in PHP (and many other languages) is executing database queries inside a loop. It works — until it doesn’t. In this tutorial, we’ll explore why it’s a bad practice, show concrete examples, and offer better alternatives.

The Problem: Query in a Loop

Let’s look at a real-world example:

$users = $this->User->find('all');

foreach ($users as $user) {
$posts = $this->Post->find('all', [
'conditions' => ['Post.user_id' => $user['User']['id']]
]);
// Do something with $posts
}

At first glance, this seems fine. But under the hood, you’re executing N+1 queries:

  • 1 query to fetch users

  • 1 query per user to fetch posts

If you have 500 users, that’s 501 queries.

Why This is a Problem

Performance

  • More queries = more latency

  • MySQL has connection limits

  • Your server burns CPU waiting for responses

Scalability

  • Code like this works in dev with 10 records

  • It will collapse in production with thousands

Complexity

  • Difficult to debug

  • Harder to optimize later

The Solution: Use Joins or Pre-Fetching

CakePHP provides ways to avoid this.

Use contain (CakePHP 2.x/3.x)

$users = $this->User->find('all', [
'contain' => ['Post']
]);

This results in 2 queries:

  • One for users

  • One for posts where user_id matches the users fetched

CakePHP merges them under each user in the result set.

Manual Pre-Fetch (when needed)

If you need even more control:

$userIds = Hash::extract($users, '{n}.User.id');

$posts = $this->Post->find('all', [
'conditions' => ['Post.user_id' => $userIds]
]);

$postsByUser = Hash::combine($posts, '{n}.Post.id', '{n}.Post', '{n}.Post.user_id');

foreach ($users as &$user) {
$user['Post'] = $postsByUser[$user['User']['id']] ?? [];
}

Use SQL JOINs Directly (if Cake isn’t handling it well)

In custom queries or performance-heavy areas:

$this->User->find('all', [
'joins' => [[
'table' => 'posts',
'alias' => 'Post',
'type' => 'LEFT',
'conditions' => ['User.id = Post.user_id']
]]
]);

When You Might Use Queries in Loops (Rarely)

Sometimes it’s necessary:

  • Processing very large datasets (batch-by-batch)

  • Real-time data that can’t be preloaded

Even then: batch your queries, use caching, and profile your code.