I found an N+1 query problem inside a login route

Recently, while programming, I noticed something strange in the route the application used to log users in: there was a loop querying the database during each iteration.

The code followed a structure similar to this:

foreach ($parentItems as $key => $parentItem) {
    $relatedItems = (new Model)
        ->table('related_items')
        ->where([
            'PARENT_ID' => $parentItem['ID'],
        ])
        ->findAll();

    $parentItems[$key]['RELATED_ITEMS'] = $relatedItems;
}

The problem

I investigated the code and understood the problem the loop was solving. However, it was solving one problem while creating another: N+1 queries.

The application first executed one query to retrieve the parent records. Then, it executed another query for every parent record to retrieve its related items.

In other words:

1 query to retrieve the parent records
+ N queries to retrieve their related records

Each additional query introduces costs such as:

• Database communication time
• Processing for joins, filters, and sorting
• Connection pool usage
• Additional load on the database server
• Increased response time

The problem was glaring: the response time grew linearly as the user had more related records.

It worked roughly like this:

1 initial query + 20 related-item queries ≈ 21 × 40 ms ≈ 840 ms

Of course, this is only an approximate example. The login route performs several other operations, and each query may have a different execution time.

Still, the example demonstrates the main issue: the number of database queries grows linearly with the number of parent records.

In terms of query count, this behavior is:

O(N) database queries

The solution

My goal was to remove the N+1 problem while preserving the original response structure.

Instead of querying the database during every iteration, I first extracted all the parent record IDs:

$ids = array_column(
    $items,
    'ID'
);

The result looks something like this:

[10, 20, 30]

I then performed a single query to retrieve all the related records:

$relatedItems = (new Model)
    ->table('related_items ri')
    ->in(['ri.PARENT_ID' => $ids])
    ->findAll([
        'ri.PARENT_ID',
        // Other fields...
    ]);

Conceptually, this generates:

WHERE ri.PARENT_IDIN (10,20,30)

Including PARENT_ID in the query result allows PHP to identify which parent record each related item belongs to.

However, retrieving the records was only part of the solution. I still needed to group them in memory:

$itemsByParent = [];

foreach ($relatedItems as $relatedItem) {
    $parentId = $relatedItem['PARENT_ID'];

    unset($relatedItem['PARENT_ID']);

    $itemsByParent[$parentId][] = $relatedItem;
}

The grouped result looks like this:

[
    10 => [
        // Items related to record 10
    ],
    20 => [
        // Items related to record 20
    ]
]

Finally, I associated the grouped items with their corresponding parent records:

foreach ($items as $key => $item) {
    $id = $item['ID'];

    $items[$key]['RELATED_ITEMS'] =
        $itemsByParent[$id] ?? [];
}

At this point, you might ask:

“Did you remove one foreach only to add two more?”

Yes—but the important difference is what happens inside those loops.

The original loop performed database I/O during every iteration. The new loops only process arrays that are already in memory. They do not establish database communication or execute additional queries.

The improvement is not related to the loops being synchronous. Database operations may also be synchronous. The real gain comes from replacing repeated database access with much cheaper in-memory processing.

Complexity analysis

Let’s consider:

• N = number of parent records
• M = total number of related records

The in-memory processing is approximately:

O(N + M)

This is expected because the application needs to iterate over both collections to organize the final result.

The main improvement is in the number of database queries:

Before: 1 + N queries
After: 2 fixed queries

Therefore, the number of queries went from O(N) to O(1).

It is important to clarify that this does not mean the total execution time is strictly O(1). As the amount of data increases, the database still needs to process and return more rows, and PHP still needs to organize them.

What remains constant is the number of database round trips.

Results

The refactoring changes the database access pattern from:

1 query for the parent records
+ 1 query for every parent record

to:

1 query for the parent records
+ 1 query for all related records

For example:

The next step is to measure the route under the same conditions before and after the change. Useful metrics include:

• Total response time
• Number of executed queries
• Database processing time
• Memory consumption
• Response time with different numbers of related records

Conclusion

This change eliminates the N+1 problem and reduces the operation from 1 + N queries to only 2 queries.

The foreach loops still exist, but they now process data in memory instead of repeatedly accessing the database. As a result, the number of database round trips remains fixed, the connection pool experiences less pressure, and the login route scales more predictably as the number of records increases.