Optimize Magento 2 Collections: The Complete Guide for Maximum Performance

A Magento 2 collection is an object that acts as a query builder to efficiently manipulate sets of entities (products, orders, customers, etc.) in the database. It allows you to construct complex SQL queries while staying within Magento’s architecture, optimizing performance by avoiding repetitive single queries that would overload MySQL

Instantiating a Collection

Best practice: Always use a CollectionFactory injected as a dependency, then call the create() method to get a new collection instance. Avoid $model->getCollection() (a Magento 1 legacy approach), as it violates modern architecture and causes issues with testability and reusability ^[2].

Example:

public function __construct(
    \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
) {
    $this->productCollectionFactory = $productCollectionFactory;
}

public function getProducts()
{
    $collection = $this->productCollectionFactory->create();
    // Manipulation de la collection...
}

Why Use Collections?

In Magento 2, all objects (models, collections, repositories) are abstractions built on top of a relational database. Every object or collection operation ultimately translates into an optimized SQL query generated by the framework ^[1][2][3].

Common misconception: Some developers mistakenly believe Magento performs individual queries in a loop, like:

This would be disastrous for performance, as it multiplies round-trips between PHP and MySQL, unnecessarily overloading the database.

The real purpose of collections is to avoid this pattern by building a single optimized query that retrieves all results at once, with filtering, sorting, pagination, joins, and more ^[2][3].

Key Collection Methods

1. addFieldToFilter($field, $condition)

Filters results (equivalent to SQL WHERE). Available operators:

• 'eq' → equals (=)
• 'neq' → not equals (!=)
• 'like' → partial match (LIKE)
• 'in' → in an array of values (IN)
• 'nin' → not in an array (NOT IN)
• 'gt' → greater than (>)
• 'lt' → less than (<)
• 'gteq' → greater than or equal (>=)
• 'lteq' → less than or equal (<=)
• 'null' → is NULL
• 'notnull' → is not NULL

$collection->addFieldToFilter('status', ['eq' => 1]);
$collection->addFieldToFilter('name', ['like' => '%promo%']);
$collection->addFieldToFilter('id', ['in' => [1,2,3]]);

2. addFieldToSelect($field)

Specifies which columns to include in the query (default: all columns) ^[3].

3. Pagination & Sorting

• setPageSize($size) → Number of items per page (LIMIT in SQL).
• setCurPage($page) → Current page number (OFFSET in SQL).
• setOrder($field, $direction) → Sorting (ASC or DESC) ^[3].

Advanced Manipulation: The Select Object

When built-in methods are insufficient, you can access the underlying Zend_Db_Select object via getSelect(). ⚠️ Important:

• Collection methods (e.g., addFieldToFilter) return the collection, allowing method chaining.
• Select methods (e.g., getSelect()->group()) return a Zend_Db_Select object, not a collection. Never reassign the result to your collection variable, or you’ll lose all collection methods.

✅ Correct:

❌ Incorrect (breaks the collection):

Key Select Methods:

// Correct
$collection->getSelect()->group('customer_id');
// Incorrect
$collection = $collection->getSelect()->group('customer_id'); // $collection is no more a collection object

group($field) → SQL GROUP BY

$collection->getSelect()->group('customer_id');

columns($fields) → SQL SELECT fields ⚠️ Warning: columns([]) replaces the selected columns. You must include all required fields, or the collection won’t hydrate objects correctly.

$collection->getSelect()->columns([
    'nb_reviews' => new \Zend_Db_Expr('COUNT(review_id)')
]);

Always use Zend_Db_Expr for SQL functions (COUNT, SUM, etc.), or Zend will escape characters and break the query.

join($table, $condition, $columns) → SQL JOIN Recommended syntax:

$collection->getSelect()->join(
    ['alias' => 'table_name'], // name of the table and it's "as"
    'main_table.id = alias.foreign_id', // condition ON
    ['field1' => 'alias.field1', 'field2' => 'alias.field2'] // selected fields renamed
);

Use a key-value array for the 3rd parameter to rename fields and avoid naming conflicts.

In summary, mastering Magento 2 collections is essential for optimizing your e-commerce site’s performance and ensuring efficient management of product, customer, or order data. By leveraging collections, you avoid costly single-row SQL queries and benefit from a single, filtered, sorted, and paginated query generated by the Magento 2 framework.

Why is this crucial? Because proper use of Magento 2 collections improves site speed, customer satisfaction, and conversion rates, while ensuring the scalability of your online store.

Want to go further with Magento 2 optimization?
Explore our other guides dedicated to Magento 2 performance, using alternative front-end themes like Breeze, and optimizing Magento 2 models: learn when to choose a plugin, an observer, or dependency injection to extend or modify Magento’s native behavior, while ensuring both maintainability and high performance for your store.