9 Magento 2 Code Patterns We Fix Most Often in Production

9 Magento 2 Code Patterns That Quietly Slow Your Store

Most Magento 2 performance problems we encounter in audits aren't caused by missing caching, undersized servers, or wrong configuration. They're caused by code patterns that work fine on small catalogs and quietly destroy performance at scale.

These patterns pass code review. They pass tests. They run fine for the first year. Then traffic doubles, the catalog grows, and suddenly checkout takes 8 seconds because someone wrote foreach ($items as $item) { $item->save(); } two years ago.

This article documents the 9 specific code patterns we fix most often in Magento 2 performance audits, based on 70+ projects since 2018 and the methodology I presented at Magento Meet Ukraine 2025. Each pattern includes the wrong way, the right way, and the underlying reason it matters.

This is a reference article. It's written for Magento developers, technical leads, and CTOs reviewing their team's code. If you're a store owner reading this — forward it to your developer with one question: "Are any of these patterns in our codebase?"

Why These Patterns Matter

Before the patterns themselves, the underlying principle: Magento 2's framework is generous to bad code at small scale and brutal at large scale.

A Model::save() call inside a loop processes 100 items in a second. The same loop processes 50,000 items in 20+ minutes. The framework's overhead per call is small but accumulates linearly. Most performance bugs in Magento 2 codebases follow this shape — they don't fail when written, they fail when the business grows.

The patterns below all share that property. Each one is something that runs fine on a developer's local environment with sample data and breaks badly in production with real-world data volumes.

Pattern 1: Replace Loops of save() with insertOnDuplicate()

The Problem

The most common performance anti-pattern in Magento 2 codebases. Every save() call generates an individual SQL statement, runs through the model's full lifecycle (events, plugins, validations), and commits separately. In a loop, this multiplies overhead linearly with item count.

Bad Code

<?php
private function saveReviews(array $reviews): void
{
    foreach ($reviews as $review) {
        if (empty($review->getData('feedback_id'))) {
            continue;
        }

        $feedbackId = $review->getData('feedback_id');
        $model = $this->reviewFactory->create()->load(
            $feedbackId,
            'feedback_id'
        );
        $model->addData($review->getData());
        $this->reviewResource->save($model);
    }
}

<?php
private function saveReviews(array $reviews): void
{
    foreach ($reviews as $review) {
        if (empty($review->getData('feedback_id'))) {
            continue;
        }

        $feedbackId = $review->getData('feedback_id');
        $model = $this->reviewFactory->create()->load(
            $feedbackId,
            'feedback_id'
        );
        $model->addData($review->getData());
        $this->reviewResource->save($model);
    }
}

<?php
private function saveReviews(array $reviews): void
{
    foreach ($reviews as $review) {
        if (empty($review->getData('feedback_id'))) {
            continue;
        }

        $feedbackId = $review->getData('feedback_id');
        $model = $this->reviewFactory->create()->load(
            $feedbackId,
            'feedback_id'
        );
        $model->addData($review->getData());
        $this->reviewResource->save($model);
    }
}

<?php
private function saveReviews(array $reviews): void
{
    foreach ($reviews as $review) {
        if (empty($review->getData('feedback_id'))) {
            continue;
        }

        $feedbackId = $review->getData('feedback_id');
        $model = $this->reviewFactory->create()->load(
            $feedbackId,
            'feedback_id'
        );
        $model->addData($review->getData());
        $this->reviewResource->save($model);
    }
}

Good Code

<?php
private function saveReviews(array $reviews): void
{
    $rows = [];
    foreach ($reviews as $review) {
        if (empty($review->getData('feedback_id'))) {
            continue;
        }
        $rows[] = $review->getData();
    }

    if (!$rows) {
        return;
    }

    $connection = $this->reviewResource->getConnection();
    $table = $this->reviewResource->getMainTable();
    $updateFields = array_diff(array_keys($rows[0]), ['feedback_id']);

    $connection->insertOnDuplicate($table, $rows, $updateFields);
}

<?php
private function saveReviews(array $reviews): void
{
    $rows = [];
    foreach ($reviews as $review) {
        if (empty($review->getData('feedback_id'))) {
            continue;
        }
        $rows[] = $review->getData();
    }

    if (!$rows) {
        return;
    }

    $connection = $this->reviewResource->getConnection();
    $table = $this->reviewResource->getMainTable();
    $updateFields = array_diff(array_keys($rows[0]), ['feedback_id']);

    $connection->insertOnDuplicate($table, $rows, $updateFields);
}

<?php
private function saveReviews(array $reviews): void
{
    $rows = [];
    foreach ($reviews as $review) {
        if (empty($review->getData('feedback_id'))) {
            continue;
        }
        $rows[] = $review->getData();
    }

    if (!$rows) {
        return;
    }

    $connection = $this->reviewResource->getConnection();
    $table = $this->reviewResource->getMainTable();
    $updateFields = array_diff(array_keys($rows[0]), ['feedback_id']);

    $connection->insertOnDuplicate($table, $rows, $updateFields);
}

<?php
private function saveReviews(array $reviews): void
{
    $rows = [];
    foreach ($reviews as $review) {
        if (empty($review->getData('feedback_id'))) {
            continue;
        }
        $rows[] = $review->getData();
    }

    if (!$rows) {
        return;
    }

    $connection = $this->reviewResource->getConnection();
    $table = $this->reviewResource->getMainTable();
    $updateFields = array_diff(array_keys($rows[0]), ['feedback_id']);

    $connection->insertOnDuplicate($table, $rows, $updateFields);
}

Why It Matters

insertOnDuplicate() sends a single SQL statement that handles both insert and update cases via MySQL's ON DUPLICATE KEY UPDATE syntax. Database round trips collapse from N to 1. Network overhead, transaction overhead, and Magento's per-save event firing all disappear.

When To Use

This pattern fits any bulk import, sync, or batch update where you're working with simple flat tables. It does not fit complex EAV entities like products with custom attributes — those need their proper repositories because the EAV structure spans multiple tables. For products, customers, and orders, use the repository pattern. For everything else with a flat structure, prefer insertOnDuplicate().

The performance difference between these two approaches in production is rarely subtle. We've seen import jobs go from "won't finish overnight" to "completes in minutes" with this single change.

Pattern 2: Bulk Updates and Deletes via Direct SQL

The Problem

Loading a collection of entities just to delete or status-update them is wasteful. Magento loads every entity into PHP memory, hydrates objects, fires events, and runs each delete or save individually.

Bad Code

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 0);

foreach ($collection as $item) {
    $item->setStatus(1);
    $item->save();
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 0);

foreach ($collection as $item) {
    $item->setStatus(1);
    $item->save();
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 0);

foreach ($collection as $item) {
    $item->setStatus(1);
    $item->save();
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 0);

foreach ($collection as $item) {
    $item->setStatus(1);
    $item->save();
}

Good Code

<?php
$ids = [1, 2, 3, 4];
$connection = $resource->getConnection();

$connection->update(
    $resource->getMainTable(),
    ['status' => 1],
    ['entity_id IN (?)' => $ids]
);

// Or for delete:
$connection->delete(
    $resource->getMainTable(),
    ['entity_id IN (?)' => $ids]
);

<?php
$ids = [1, 2, 3, 4];
$connection = $resource->getConnection();

$connection->update(
    $resource->getMainTable(),
    ['status' => 1],
    ['entity_id IN (?)' => $ids]
);

// Or for delete:
$connection->delete(
    $resource->getMainTable(),
    ['entity_id IN (?)' => $ids]
);

<?php
$ids = [1, 2, 3, 4];
$connection = $resource->getConnection();

$connection->update(
    $resource->getMainTable(),
    ['status' => 1],
    ['entity_id IN (?)' => $ids]
);

// Or for delete:
$connection->delete(
    $resource->getMainTable(),
    ['entity_id IN (?)' => $ids]
);

<?php
$ids = [1, 2, 3, 4];
$connection = $resource->getConnection();

$connection->update(
    $resource->getMainTable(),
    ['status' => 1],
    ['entity_id IN (?)' => $ids]
);

// Or for delete:
$connection->delete(
    $resource->getMainTable(),
    ['entity_id IN (?)' => $ids]
);

Why It Matters

A direct SQL update or delete on a flat table executes in milliseconds regardless of row count. The collection-loop approach scales linearly with row count and consumes memory proportionally.

When To Use

Same caveat as Pattern 1: this only applies to flat tables. EAV entities and business entities that have observers/plugins doing real work should still go through repositories.

For very large operations, chunk the IDs in batches of 500-1000 and wrap each chunk in a transaction. After mass changes, you may need to invalidate relevant indexes or cache tags manually since you've bypassed the model's lifecycle events.

Pattern 3: Don't Load Full Collections When You Need a Slice

The Problem

Loading every column from every row when you only need two columns from 100 rows. Magento collections default to selecting all fields and loading the full result set into memory.

Bad Code

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

foreach ($collection as $item) {
    $sku = $item->getSku();
    // ... process sku
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

foreach ($collection as $item) {
    $sku = $item->getSku();
    // ... process sku
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

foreach ($collection as $item) {
    $sku = $item->getSku();
    // ... process sku
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

foreach ($collection as $item) {
    $sku = $item->getSku();
    // ... process sku
}

Good Code

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToSelect(['entity_id', 'sku'])
    ->addFieldToFilter('status', 1)
    ->setPageSize(500)
    ->setCurPage(1);

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToSelect(['entity_id', 'sku'])
    ->addFieldToFilter('status', 1)
    ->setPageSize(500)
    ->setCurPage(1);

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToSelect(['entity_id', 'sku'])
    ->addFieldToFilter('status', 1)
    ->setPageSize(500)
    ->setCurPage(1);

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToSelect(['entity_id', 'sku'])
    ->addFieldToFilter('status', 1)
    ->setPageSize(500)
    ->setCurPage(1);

Why It Matters

Selecting only required fields reduces SQL transfer size, PHP memory consumption, and object hydration cost. Pagination prevents loading 100,000 rows into memory when you'll process them in chunks anyway.

When To Use

Anywhere you iterate a collection but only need specific fields. The addFieldToSelect() method is your friend — use it explicitly rather than relying on defaults.

For very large datasets, combine pagination with iteration:

<?php
$pageSize = 500;
$currentPage = 1;

do {
    $collection = $this->collectionFactory->create()
        ->addFieldToSelect(['entity_id', 'sku'])
        ->addFieldToFilter('status', 1)
        ->setPageSize($pageSize)
        ->setCurPage($currentPage);

    foreach ($collection as $item) {
        // process
    }

    $currentPage++;
} while ($collection->getSize() > $pageSize * ($currentPage - 1));

<?php
$pageSize = 500;
$currentPage = 1;

do {
    $collection = $this->collectionFactory->create()
        ->addFieldToSelect(['entity_id', 'sku'])
        ->addFieldToFilter('status', 1)
        ->setPageSize($pageSize)
        ->setCurPage($currentPage);

    foreach ($collection as $item) {
        // process
    }

    $currentPage++;
} while ($collection->getSize() > $pageSize * ($currentPage - 1));

<?php
$pageSize = 500;
$currentPage = 1;

do {
    $collection = $this->collectionFactory->create()
        ->addFieldToSelect(['entity_id', 'sku'])
        ->addFieldToFilter('status', 1)
        ->setPageSize($pageSize)
        ->setCurPage($currentPage);

    foreach ($collection as $item) {
        // process
    }

    $currentPage++;
} while ($collection->getSize() > $pageSize * ($currentPage - 1));

<?php
$pageSize = 500;
$currentPage = 1;

do {
    $collection = $this->collectionFactory->create()
        ->addFieldToSelect(['entity_id', 'sku'])
        ->addFieldToFilter('status', 1)
        ->setPageSize($pageSize)
        ->setCurPage($currentPage);

    foreach ($collection as $item) {
        // process
    }

    $currentPage++;
} while ($collection->getSize() > $pageSize * ($currentPage - 1));

This processes any size dataset with bounded memory.

Pattern 4: Wrap Bulk Operations in Transactions

The Problem

Multiple write operations without an explicit transaction means each operation commits separately. This adds round-trip overhead per statement and provides no rollback safety if one step fails midway.

Bad Code

<?php
$connection->insertMultiple($tableA, $rowsA);
$connection->insertOnDuplicate($tableB, $rowsB, ['qty']);
$connection->update($tableC, ['flag' => 1], ['id IN (?)' => $ids]);
// If any of these fails, the others are already committed

<?php
$connection->insertMultiple($tableA, $rowsA);
$connection->insertOnDuplicate($tableB, $rowsB, ['qty']);
$connection->update($tableC, ['flag' => 1], ['id IN (?)' => $ids]);
// If any of these fails, the others are already committed

<?php
$connection->insertMultiple($tableA, $rowsA);
$connection->insertOnDuplicate($tableB, $rowsB, ['qty']);
$connection->update($tableC, ['flag' => 1], ['id IN (?)' => $ids]);
// If any of these fails, the others are already committed

<?php
$connection->insertMultiple($tableA, $rowsA);
$connection->insertOnDuplicate($tableB, $rowsB, ['qty']);
$connection->update($tableC, ['flag' => 1], ['id IN (?)' => $ids]);
// If any of these fails, the others are already committed

Good Code

<?php
$connection = $resource->getConnection();
$connection->beginTransaction();

try {
    $connection->insertMultiple($tableA, $rowsA);
    $connection->insertOnDuplicate($tableB, $rowsB, ['qty']);
    $connection->update($tableC, ['flag' => 1], ['id IN (?)' => $ids]);
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollBack();
    throw $e;
}

<?php
$connection = $resource->getConnection();
$connection->beginTransaction();

try {
    $connection->insertMultiple($tableA, $rowsA);
    $connection->insertOnDuplicate($tableB, $rowsB, ['qty']);
    $connection->update($tableC, ['flag' => 1], ['id IN (?)' => $ids]);
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollBack();
    throw $e;
}

<?php
$connection = $resource->getConnection();
$connection->beginTransaction();

try {
    $connection->insertMultiple($tableA, $rowsA);
    $connection->insertOnDuplicate($tableB, $rowsB, ['qty']);
    $connection->update($tableC, ['flag' => 1], ['id IN (?)' => $ids]);
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollBack();
    throw $e;
}

<?php
$connection = $resource->getConnection();
$connection->beginTransaction();

try {
    $connection->insertMultiple($tableA, $rowsA);
    $connection->insertOnDuplicate($tableB, $rowsB, ['qty']);
    $connection->update($tableC, ['flag' => 1], ['id IN (?)' => $ids]);
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollBack();
    throw $e;
}

Why It Matters

Two benefits. First, the transaction reduces overall write overhead because all operations commit at once instead of individually. Second, and more importantly, you get atomicity — either all the operations succeed or none of them do.

When To Use

Any time you have related writes across multiple tables that need to stay consistent. Stock updates that touch multiple tables. Order processing that creates rows in sales_order and related tables. Any sync that updates multiple records together.

The catch: don't wrap operations that take minutes inside a single transaction. Long-running transactions hold locks and can block other queries. For very large bulk operations, chunk into smaller transactions of a few thousand rows each.

Pattern 5: Aggregate in SQL, Not in PHP

The Problem

Loading thousands of rows into PHP just to sum a column or count items. Every row crosses the network, gets hydrated into an object, and the calculation happens after all that overhead.

Bad Code

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('customer_id', $customerId);

$total = 0;
foreach ($collection as $item) {
    $total += $item->getQty();
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('customer_id', $customerId);

$total = 0;
foreach ($collection as $item) {
    $total += $item->getQty();
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('customer_id', $customerId);

$total = 0;
foreach ($collection as $item) {
    $total += $item->getQty();
}

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('customer_id', $customerId);

$total = 0;
foreach ($collection as $item) {
    $total += $item->getQty();
}

Good Code

<?php
$connection = $resource->getConnection();

$total = (float) $connection->fetchOne(
    $connection->select()
        ->from($table, ['SUM(qty)'])
        ->where('customer_id = ?', $customerId)
);

<?php
$connection = $resource->getConnection();

$total = (float) $connection->fetchOne(
    $connection->select()
        ->from($table, ['SUM(qty)'])
        ->where('customer_id = ?', $customerId)
);

<?php
$connection = $resource->getConnection();

$total = (float) $connection->fetchOne(
    $connection->select()
        ->from($table, ['SUM(qty)'])
        ->where('customer_id = ?', $customerId)
);

<?php
$connection = $resource->getConnection();

$total = (float) $connection->fetchOne(
    $connection->select()
        ->from($table, ['SUM(qty)'])
        ->where('customer_id = ?', $customerId)
);

Why It Matters

Databases are extraordinarily good at aggregation. They use indexes, run optimized internal algorithms, and return a single value over the network instead of thousands of rows. PHP doing the same work loses on every dimension — slower, more memory, more network traffic.

When To Use

Any sum, average, count, min, max, or grouped calculation. If you find yourself loading rows just to add up a column, stop and write SQL instead. The framework's collection abstraction is convenient but it costs you.

This is one of the easiest patterns to find during code review — search for foreach.*\+= or foreach.*->getCount() patterns in the codebase. Almost every match is a candidate for SQL aggregation.

Pattern 6: Use JOINs Instead of Queries in Loops

The Problem

Fetching parent records, then looping through them and running a separate query per record to get related data. This is the "N+1 query" problem, and it's widespread in custom Magento code.

Bad Code

<?php
$orders = $this->orderRepository->getList($criteria)->getItems();

foreach ($orders as $order) {
    $shippingAddress = $this->addressRepository
        ->getByOrderIdAndType($order->getEntityId(), 'shipping');
    // ... process address
}

<?php
$orders = $this->orderRepository->getList($criteria)->getItems();

foreach ($orders as $order) {
    $shippingAddress = $this->addressRepository
        ->getByOrderIdAndType($order->getEntityId(), 'shipping');
    // ... process address
}

<?php
$orders = $this->orderRepository->getList($criteria)->getItems();

foreach ($orders as $order) {
    $shippingAddress = $this->addressRepository
        ->getByOrderIdAndType($order->getEntityId(), 'shipping');
    // ... process address
}

<?php
$orders = $this->orderRepository->getList($criteria)->getItems();

foreach ($orders as $order) {
    $shippingAddress = $this->addressRepository
        ->getByOrderIdAndType($order->getEntityId(), 'shipping');
    // ... process address
}

Good Code

<?php
$select = $connection->select()
    ->from(['o' => $orderTable], ['entity_id', 'increment_id'])
    ->joinLeft(
        ['a' => $addressTable],
        'a.parent_id = o.entity_id AND a.address_type = "shipping"',
        ['country_id', 'city', 'postcode']
    );

$rows = $connection->fetchAll($select);

<?php
$select = $connection->select()
    ->from(['o' => $orderTable], ['entity_id', 'increment_id'])
    ->joinLeft(
        ['a' => $addressTable],
        'a.parent_id = o.entity_id AND a.address_type = "shipping"',
        ['country_id', 'city', 'postcode']
    );

$rows = $connection->fetchAll($select);

<?php
$select = $connection->select()
    ->from(['o' => $orderTable], ['entity_id', 'increment_id'])
    ->joinLeft(
        ['a' => $addressTable],
        'a.parent_id = o.entity_id AND a.address_type = "shipping"',
        ['country_id', 'city', 'postcode']
    );

$rows = $connection->fetchAll($select);

<?php
$select = $connection->select()
    ->from(['o' => $orderTable], ['entity_id', 'increment_id'])
    ->joinLeft(
        ['a' => $addressTable],
        'a.parent_id = o.entity_id AND a.address_type = "shipping"',
        ['country_id', 'city', 'postcode']
    );

$rows = $connection->fetchAll($select);

Why It Matters

The bad version generates 1 query for the orders plus N queries for each order's address — for 1000 orders, that's 1001 database round trips. The good version generates 1 query that returns everything joined.

Each query has fixed overhead — connection time, parsing, planning, network. Doing it 1001 times instead of 1 is slow even if the individual queries are fast. On busy stores, this is the single biggest cause of slow admin grids and slow API responses.

When To Use

Anywhere you find yourself iterating items and querying related data inside the loop. The repository pattern is convenient but encourages this anti-pattern. For performance-critical code paths, drop down to direct SQL with JOINs.

The trade-off: direct SQL bypasses model events. If your related entities have important event listeners, you'll need to handle that separately. For read-only data aggregation, direct SQL is almost always the right choice.

Pattern 7: Cache Expensive Computations and Config Builds

The Problem

Recalculating the same expensive value on every request. Building configuration objects, parsing complex data structures, or computing values that change rarely — all common candidates that get repeated needlessly.

Bad Code

<?php
public function getProcessedConfig(): array
{
    return $this->heavyService->buildConfig();
    // Called on every request, takes 200ms every time
}

<?php
public function getProcessedConfig(): array
{
    return $this->heavyService->buildConfig();
    // Called on every request, takes 200ms every time
}

<?php
public function getProcessedConfig(): array
{
    return $this->heavyService->buildConfig();
    // Called on every request, takes 200ms every time
}

<?php
public function getProcessedConfig(): array
{
    return $this->heavyService->buildConfig();
    // Called on every request, takes 200ms every time
}

Good Code

<?php
public function getProcessedConfig(): array
{
    $key = 'mymodule_config_v1';

    if (($data = $this->cache->load($key)) === false) {
        $data = $this->heavyService->buildConfig();
        $this->cache->save(
            json_encode($data),
            $key,
            ['MISC'],
            3600
        );
    } else {
        $data = json_decode($data, true);
    }

    return $data;
}

<?php
public function getProcessedConfig(): array
{
    $key = 'mymodule_config_v1';

    if (($data = $this->cache->load($key)) === false) {
        $data = $this->heavyService->buildConfig();
        $this->cache->save(
            json_encode($data),
            $key,
            ['MISC'],
            3600
        );
    } else {
        $data = json_decode($data, true);
    }

    return $data;
}

<?php
public function getProcessedConfig(): array
{
    $key = 'mymodule_config_v1';

    if (($data = $this->cache->load($key)) === false) {
        $data = $this->heavyService->buildConfig();
        $this->cache->save(
            json_encode($data),
            $key,
            ['MISC'],
            3600
        );
    } else {
        $data = json_decode($data, true);
    }

    return $data;
}

<?php
public function getProcessedConfig(): array
{
    $key = 'mymodule_config_v1';

    if (($data = $this->cache->load($key)) === false) {
        $data = $this->heavyService->buildConfig();
        $this->cache->save(
            json_encode($data),
            $key,
            ['MISC'],
            3600
        );
    } else {
        $data = json_decode($data, true);
    }

    return $data;
}

Why It Matters

Magento's cache framework already supports this pattern via Redis (or whatever backend you've configured). The first request pays the computation cost. Every subsequent request — until the cache expires or gets invalidated — gets the cached result in microseconds.

When To Use

Any read-heavy computation that's expensive but changes infrequently. Configuration objects assembled from multiple sources. Pricing rule resolution. Tax computation tables. Module availability checks. Computed navigation structures.

Two important details. First, choose the cache lifetime carefully — too long and stale data shows; too short and you barely cache. Second, define cache tags properly so invalidation works when underlying data changes. The ['MISC'] tag in the example is a placeholder — use specific tags relevant to your data so cache cleaning works correctly.

Pattern 8: Use fetchCol and fetchPairs Instead of fetchAll When Possible

The Problem

Using fetchAll() when you only need a single column or a key-value mapping. Magento's database adapter has more efficient methods for these specific cases that most developers don't use.

Bad Code

<?php
$rows = $connection->fetchAll(
    $connection->select()->from($table, ['entity_id'])->where('status = 1')
);

$ids = array_column($rows, 'entity_id');

<?php
$rows = $connection->fetchAll(
    $connection->select()->from($table, ['entity_id'])->where('status = 1')
);

$ids = array_column($rows, 'entity_id');

<?php
$rows = $connection->fetchAll(
    $connection->select()->from($table, ['entity_id'])->where('status = 1')
);

$ids = array_column($rows, 'entity_id');

<?php
$rows = $connection->fetchAll(
    $connection->select()->from($table, ['entity_id'])->where('status = 1')
);

$ids = array_column($rows, 'entity_id');

Good Code

<?php
// When you need just one column:
$ids = $connection->fetchCol(
    $connection->select()->from($table, 'entity_id')->where('status = 1')
);

// When you need a key-value mapping:
$skuToId = $connection->fetchPairs(
    $connection->select()->from($table, ['sku', 'entity_id'])
);

<?php
// When you need just one column:
$ids = $connection->fetchCol(
    $connection->select()->from($table, 'entity_id')->where('status = 1')
);

// When you need a key-value mapping:
$skuToId = $connection->fetchPairs(
    $connection->select()->from($table, ['sku', 'entity_id'])
);

<?php
// When you need just one column:
$ids = $connection->fetchCol(
    $connection->select()->from($table, 'entity_id')->where('status = 1')
);

// When you need a key-value mapping:
$skuToId = $connection->fetchPairs(
    $connection->select()->from($table, ['sku', 'entity_id'])
);

<?php
// When you need just one column:
$ids = $connection->fetchCol(
    $connection->select()->from($table, 'entity_id')->where('status = 1')
);

// When you need a key-value mapping:
$skuToId = $connection->fetchPairs(
    $connection->select()->from($table, ['sku', 'entity_id'])
);

Why It Matters

fetchCol() returns a flat array of values — no associative structure, no per-row arrays, just the values you wanted. fetchPairs() returns a key-value associative array directly. Both are dramatically more memory-efficient than fetchAll() followed by post-processing.

The difference compounds with row count. For 10,000 rows, fetchCol() returns a 10,000-element flat array. fetchAll() returns a 10,000-element array of single-key associative arrays — significantly more memory and slower iteration.

When To Use

fetchCol() whenever you need exactly one column from a query. fetchPairs() whenever you need a lookup map between two values. These two methods cover a surprising number of real use cases that developers default to fetchAll() for.

Pattern 9: Use getSize() Instead of Loading Collections to Count

The Problem

Loading an entire collection into memory just to call count() on it. Magento collections support a much more efficient method for this.

Bad Code

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

$count = count($collection);
// or:
$count = $collection->count();

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

$count = count($collection);
// or:
$count = $collection->count();

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

$count = count($collection);
// or:
$count = $collection->count();

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

$count = count($collection);
// or:
$count = $collection->count();

Good Code

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

$count = (int) $collection->getSize();

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

$count = (int) $collection->getSize();

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

$count = (int) $collection->getSize();

<?php
$collection = $this->collectionFactory->create()
    ->addFieldToFilter('status', 1);

$count = (int) $collection->getSize();

Why It Matters

getSize() runs a SELECT COUNT(*) query and returns the integer. The collection itself is never loaded — no rows fetched, no objects hydrated, no memory consumed for the data.

count() (and PHP's count() on a collection) forces the collection to load all matching rows into memory just to count them. On a query matching 50,000 rows, that's 50,000 unnecessary object hydrations.

When To Use

Anywhere you need a count without iterating the data. Pagination calculations, conditional logic ("if there are any pending orders, show this widget"), reporting queries that just need totals.

If you're going to iterate the collection anyway, using count() after iteration is fine — the data is already loaded. The pattern only matters when the count is the only thing you need.

How to Find These Patterns in Your Codebase

If you're auditing your own code or someone else's, here's where to look first:

Cron jobs and console commands — long-running scripts are where bulk patterns matter most. Open every crontab.xml and check the underlying classes.

Import and sync code — anywhere you see foreach looping over external data, look for save() calls and missing transactions.

Admin grid backend code — admin grids are the biggest source of N+1 query problems we encounter. They handle pagination poorly when developers customize the data source.

Custom REST API endpoints — high-traffic API endpoints amplify any inefficiency. A bad collection load that takes 200ms in admin becomes a serious problem when called 10,000 times per hour.

Third-party module integrations — any custom code that bridges Magento with external systems (ERP, PIM, marketing tools) tends to accumulate these patterns as features get added over time.

Profiling: How to Find What's Actually Slow

These patterns are common, but they're not always the slowest thing in your codebase. Before refactoring based on this article, profile first.

The two tools we use most often in Magento performance work:

Blackfire — function-level profiling with call graphs. Shows you exactly which method calls are consuming time, in what order, with what arguments. The most accurate way to identify hot paths in production-like environments.

New Relic APM — continuous monitoring across all requests. Less detailed than Blackfire for any single request but excellent at surfacing patterns across thousands of requests. The best tool for catching problems that only manifest under specific conditions.

Magento's built-in Profiler is also useful for quick diagnostics, especially for cron and console commands, but it's coarser than Blackfire.

Profile first, refactor what's actually hot. Don't refactor based on theory — these patterns are common but they're not always the bottleneck in your specific codebase.

When These Patterns Aren't the Real Problem

Sometimes code-level fixes don't help because the underlying issue is architectural. If you've worked through these patterns and performance is still poor, look for:

Wrong data store for the workload. Some data shouldn't be in MySQL at all — high-volume metrics, search indexes, session data. Move them to Redis, OpenSearch, or dedicated services.

Synchronous external API calls. If your code makes HTTP calls to ERP, shipping, or tax services during a customer request, those calls dominate response time regardless of code quality. Move them async.

Server resources mismatched to load. No code change saves you from a server that's CPU-starved or memory-starved. Profile the infrastructure, not just the code.

Magento version too old. Older Magento versions have known performance issues that newer versions fixed. If you're on 2.4.4 or older, upgrading often gives you improvements that no code refactor can match.

For deeper diagnosis of performance issues that go beyond code patterns, see our 12-point Magento audit checklist and the broader Magento store rescue framework.

What This Means for Your Codebase

If you've worked through this article and recognized patterns from your own code, the next step depends on how widespread the issues are.

A few isolated cases: refactor opportunistically. Fix the patterns when you're already working in that area of code.

Patterns repeated across many modules: worth a dedicated refactor sprint. The cumulative performance gain is usually significant, and consistent patterns make future maintenance easier.

Patterns in vendor or third-party code: harder to fix because you can't modify the source. Options include using plugins to override the worst offenders, replacing the module with a better-written alternative, or accepting the performance cost and scaling infrastructure to compensate.

Pervasive patterns throughout the codebase: this is a sign of deeper code quality issues, and individual pattern fixes won't solve it. Consider a structured code audit to assess the overall state of the codebase.

Want an outside review of your Magento codebase? Book a 30-minute discovery call — we'll review your code patterns, profiling data, and performance metrics, and tell you which optimizations would have the biggest impact on your specific store.

The patterns in this article are common across Magento 2 codebases because they're easy to write and slow to surface. The teams that build fast, scalable Magento stores aren't using fundamentally different code — they're consistently avoiding these specific patterns, code review by code review, project by project.