EAV Performance

The N+1 Problem

// BAD: Loading attributes one by one
$products = $collection->load(); // 1 query
foreach ($products as $product) {
    echo $product->getName();    // N queries if not loaded
    echo $product->getPrice();   // N queries if not loaded
}
// Total: 1 + 2N queries

EAV JOIN Overhead

-- Loading a product with all attributes requires multiple JOINs
SELECT 
    p.entity_id,
    v71.value AS name,
    v77.value AS sku,
    d75.value AS price,
    i97.value AS status,
    i99.value AS visibility
FROM catalog_product_entity p
LEFT JOIN catalog_product_entity_varchar v71 
    ON v71.entity_id = p.entity_id AND v71.attribute_id = 71
LEFT JOIN catalog_product_entity_varchar v77 
    ON v77.entity_id = p.entity_id AND v77.attribute_id = 77
LEFT JOIN catalog_product_entity_decimal d75 
    ON d75.entity_id = p.entity_id AND d75.attribute_id = 75
LEFT JOIN catalog_product_entity_int i97 
    ON i97.entity_id = p.entity_id AND i97.attribute_id = 97
LEFT JOIN catalog_product_entity_int i99 
    ON i99.entity_id = p.entity_id AND i99.attribute_id = 99
WHERE p.entity_id = 123;
-- 6 JOINs for 5 attributes

Performance Comparison

Operation              | EAV (ms) | Flat (ms) | Improvement
----------------------|----------|-----------|------------
Product listing (20)  | 150      | 15        | 10x
Product search        | 200      | 20        | 10x
Category page         | 120      | 12        | 10x
Product load (1)      | 50       | 5         | 10x

Select Specific Attributes

// BAD: Load all attributes
$collection->load(); // Loads everything

// GOOD: Load only needed attributes
$collection->addAttributeToSelect(['name', 'price', 'sku', 'image']);
$collection->load();
// Only JOINs on these 4 attributes

Use Flat Tables for Listing

// Using flat collection factory (inject via constructor)
class ProductLister
{
    public function __construct(
        private \Magento\Catalog\Model\ResourceModel\Product\Flat\CollectionFactory $flatCollectionFactory
    ) {}

    public function listProducts(): void
    {
        $flatCollection = $this->flatCollectionFactory->create();
        $flatCollection->addFieldToFilter('status', 1)
            ->addFieldToFilter('visibility', ['in' => [2, 4]])
            ->setPageSize(20)
            ->setCurPage(1);
        // No EAV JOINs required
    }
}

Optimize Collection Queries

// Use field filters for indexed data
$collection->addFieldToFilter('status', 1); // Uses flat/index
$collection->addAttributeToFilter('color', 'red'); // Uses EAV JOIN

// Use WHERE conditions directly
$collection->getSelect()->where('e.entity_id > ?', 100);

// Limit columns
$collection->getSelect()->columns([
    'entity_id',
    'name',
    'price'
]);

// Use subquery for filtering
$subquery = $connection->select()
    ->from('catalog_product_index_price', ['entity_id'])
    ->where('price < ?', 50);

$collection->getSelect()->where('e.entity_id IN (?)', $subquery);

Pagination Optimization

// Use cursor-based pagination for large datasets
$collection->setPageSize(100);

$lastId = 0;
while (true) {
    $collection->addFieldToFilter('entity_id', ['gt' => $lastId]);
    $collection->load();

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

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

    $collection->clear();
}

Lazy Loading

// Default: attributes loaded on demand
$product->getName(); // Loads from DB if not cached
$product->getPrice(); // Loads from DB if not cached

// Check if loaded
$product->hasData('name'); // true if loaded

Eager Loading

// Load specific attributes upfront
$product->load(123, ['name', 'price', 'sku']);
// Only loads these 3 attributes

// Load all attributes
$product->load(123, '*');
// Loads everything (slower)

Attribute Caching

// Using cache manager (inject via constructor)
class ProductAttributeCache
{
    public function __construct(
        private \Magento\Framework\Cache\Manager $cacheManager
    ) {}

    public function getAttributes(int $productId, array $productData): array
    {
        $cacheKey = 'product_' . $productId . '_attributes';
        $cachedData = $this->cacheManager->load($cacheKey);

        if ($cachedData) {
            return unserialize($cachedData);
        }

        $this->cacheManager->save(serialize($productData), $cacheKey, [], 3600);
        return $productData;
    }
}

Batch Attribute Loading

// Load attributes for multiple products at once
$collection = $productCollection->create();
$collection->addAttributeToSelect(['name', 'price', 'sku'])
    ->addFieldToFilter('entity_id', ['in' => [1, 2, 3, 4, 5]]);

// Single query loads all attributes for all products
foreach ($collection as $product) {
    echo $product->getName(); // Already loaded
}

Index-Based Loading

// Using index resource (inject via constructor)
class ProductPriceIndex
{
    public function __construct(
        private \Magento\Catalog\Model\ResourceModel\Product\Index\Price $priceIndexResource
    ) {}

    public function loadPrice(int $productId): array
    {
        $product = $this->productFactory->create()->load($productId);
        return $this->priceIndexResource->loadByProduct($product);
    }
}

// For collections, use index join
$collection->joinPriceIndex();
// Joins catalog_product_index_price for fast price access

Index Types and Impact

Index                    | Updates On           | Impact
------------------------|---------------------|--------
catalog_product_flat    | Product save         | Table rebuild
catalog_product_index_eav | Product save       | Index update
catalog_product_index_price | Product save     | Price computation
catalog_category_product_index | Category save | Mapping update
catalogsearch_fulltext  | Product save         | Search index

Index Configuration

// Set index mode
bin/magento indexer:set-mode realtime catalog_product_price
bin/magento indexer:set-mode schedule catalog_product_price

// Realtime: updates on every save (slower saves, always fresh)
// Schedule: updates via cron (faster saves, slightly stale)

Monitoring Index Performance

-- Check index status
SELECT * FROM mview_state;

-- Check index table sizes
SELECT 
    table_name,
    table_rows,
    ROUND(data_length/1024/1024, 2) AS data_mb
FROM information_schema.tables
WHERE table_name LIKE '%index%'
ORDER BY data_length DESC;

-- Check index freshness
SELECT 
    indexer_id,
    UPDATED_AT,
    status
FROM mview_state
WHERE status = 'working';

Performance Tuning Checklist

1. Use flat tables for product listing
2. Select only needed attributes (addAttributeToSelect)
3. Use indexed columns for filtering
4. Enable scheduled indexing for large catalogs
5. Monitor slow query log
6. Use cursor-based pagination
7. Cache frequently accessed attributes
8. Optimize JOIN operations
9. Use covering indexes
10. Profile with EXPLAIN