Skip to content
intermediate Phase 77 · Performance Fundamentals

Collection Optimization

45m
1 problems
Topic Progress 0%

Select Specific Fields

addAttributeToSelect

// BAD: Select all attributes
$collection->addAttributeToSelect('*');

// GOOD: Select specific fields
$collection->addAttributeToSelect([
    'entity_id',
    'sku',
    'name',
    'price',
    'status',
    'visibility',
]);

Join Fields

// Join price from index table
$collection->joinField(
    'price',
    'catalog_product_index_price',
    'price',
    'entity_id=entity_id',
    null,
    'left'
);

// Join specific attribute
$collection->joinTable(
    'catalog_product_entity_varchar',
    'entity_id=entity_id',
    ['name' => 'value'],
    ['attribute_id = ?' => 71]
);

Key Points

  • Select only fields you need
  • Use joins for related table data
  • Avoid SELECT * in production
  • Consider memory vs query complexity

Limit Queries

Pagination

$collection->setPageSize(20);
$collection->setCurPage(1);
$totalPages = $collection->getLastPageNumber();
$products = $collection->load();

Cursor-Based Pagination

$lastId = 0;
$pageSize = 100;

while (true) {
    $collection = $this->productCollectionFactory->create();
    $collection->addFieldToFilter('entity_id', ['gt' => $lastId]);
    $collection->setOrder('entity_id', 'ASC');
    $collection->setPageSize($pageSize);
    $collection->load();

    if ($collection->count() === 0) {
        break;
    }

    foreach ($collection as $product) {
        $lastId = $product->getId();
    }
}

Key Points

  • Always use pagination for large datasets
  • Cursor-based pagination is more efficient
  • Set appropriate page sizes
  • Consider memory usage with large pages

Indexed Searches

Use Indexed Columns

// Filter on indexed column
$collection->addFieldToFilter('status', 1);
$collection->addFieldToFilter('visibility', 4);

Category Index

$collection = $this->productCollectionFactory->create();
$collection->addCategoriesFilter(['in' => $categoryId]);

Key Points

  • Filter on indexed columns for speed
  • Use category index for product-category queries
  • Avoid LIKE on large text columns
  • Consider fulltext indexes for search

Query Building Best Practices

Optimize Select Query

$select = $this->connection->select()
    ->from('catalog_product_entity', ['entity_id', 'sku', 'name'])
    ->where('status = ?', 1)
    ->order('entity_id DESC')
    ->limit(20);

Avoid SELECT in Subqueries

// BAD
$select->where('entity_id IN (?)',
    $connection->select()->from('table', ['entity_id'])
);

// GOOD
$select->where('entity_id IN (?)', [1, 2, 3]);

Key Points

  • Build queries incrementally
  • Use indexed columns in WHERE clauses
  • Avoid subqueries when possible
  • Test generated SQL for efficiency

Practice Problems

0 / 1 solved
Optimize Product Collection

Optimize a product collection that loads too much data.

Solution 
// Optimized version:
$collection = $this->productCollectionFactory->create();
$collection->addAttributeToSelect(['entity_id', 'sku', 'name', 'price']);
$collection->addFieldToFilter('status', 1);
$collection->setPageSize(20);
$collection->setCurPage(1);
$collection->load();

Quiz

1. Why avoid addAttributeToSelect('*')?

Question 1 options

2. What is cursor-based pagination?

Question 2 options

3. Why use indexed columns in filters?

Question 3 options

4. When to use setPageSize?

Question 4 options

Flashcards

Question

Select only needed fields?

Click to reveal answer

Answer

addAttributeToSelect(['name', 'price', 'sku'])

Question

Cursor-based pagination?

Click to reveal answer

Answer

Use last ID instead of OFFSET for efficiency

Question

Indexed column benefit?

Click to reveal answer

Answer

Faster WHERE clause execution

Question

Collection optimization?

Click to reveal answer

Answer

Select fields, filter early, paginate

Revision Notes

Key Takeaways

  • 1. Select only needed fields to reduce memory
  • 2. Use pagination for large result sets
  • 3. Filter on indexed columns for speed
  • 4. Build queries incrementally

Interview Tips

  • • Explain field selection impact on performance
  • • Discuss pagination strategies
  • • Know how to optimize collection queries

Cheat Sheet

Collection Optimization

  • Select: addAttributeToSelect(['field1', 'field2'])
  • Paginate: setPageSize(20)
  • Filter: use indexed columns
  • Cursor: use last ID for large datasets