Skip to content
advanced Phase 80 · Scaling Advanced

Large Catalog Optimization

Large catalog optimization including catalog performance, EAV optimization, indexing strategy, and search configuration

1h
0 problems
Topic Progress 0%

Large Catalog Performance Challenges

Performance at Scale

Catalog Size    | Challenges
────────────────|─────────────────────────────────
<10K products   | Standard config works fine
10K-100K        | Need flat tables, indexing tuning
100K-1M         | Search optimization, cache strategy
>1M products    | Full architecture review required

Database Impact

-- EAV JOIN overhead grows with catalog size
-- 100K products × 100 attributes = 10M rows per type

SELECT COUNT(*) FROM catalog_product_entity_varchar;
-- Returns: 10,000,000+

-- Query performance degrades
-- Need: Flat tables, indexing, query optimization

Key Bottlenecks

1. EAV JOINs: Multiple JOINs per product load
2. Full-text search: Index rebuild time
3. Reindexing: Catalog reindex duration
4. Category pages: Collection loading
5. Admin grid: Entity listing performance

EAV Optimization for Large Catalogs

Flat Table Strategy

// Enable flat tables for categories
bin/magento config:set catalog/frontend/flat_catalog_category 1

// Flat tables for products (if <100K products)
bin/magento config:set catalog/frontend/flat_catalog_product 1

// Note: Flat tables disabled by default in Magento 2.4+
// Use catalog/product index for read performance

Attribute Loading Optimization

// Load only needed attributes
$collection = $productCollection->create();
$collection->addAttributeToSelect(['name', 'price', 'sku', 'image', 'status']);
$collection->addFieldToFilter('status', 1);
$collection->addFieldToFilter('visibility', ['in' => [2, 4]]);

// Use index for price filtering
$collection->joinPriceIndex();

// Limit results
$collection->setPageSize(20)->setCurPage(1);

EAV Table Partitioning

-- Partition by entity_id range for large tables
ALTER TABLE catalog_product_entity_varchar
PARTITION BY RANGE (entity_id) (
    PARTITION p0 VALUES LESS THAN (100000),
    PARTITION p1 VALUES LESS THAN (200000),
    PARTITION p2 VALUES LESS THAN (300000),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Indexing Strategy

Index Mode Configuration

# Use scheduled indexing for large catalogs
bin/magento indexer:set-mode schedule catalog_product_price
bin/magento indexer:set-mode schedule catalog_product_attribute
bin/magento indexer:set-mode schedule catalogsearch_fulltext

# Realtime for critical indexes
bin/magento indexer:set-mode realtime catalog_category_product

Index Performance Tuning

// Increase memory for reindex
// bin/magento indexer:reindex --memory-limit=2G

// Monitor reindex progress
bin/magento indexer:status

// Check index health
bin/magento index:reindex catalog_product_price

Bulk Index Operations

// Batch product saves for indexing efficiency
$batchSize = 100;
$products = [];

foreach ($largeDataset as $item) {
    $products[] = $item;
    if (count($products) >= $batchSize) {
        $this->saveBatch($products);
        $products = [];
    }
}
// Single reindex for batch vs individual reindex per save

Search for Large Catalogs

Search Configuration

// app/etc/env.php
'search' => [
    'engine' => 'elasticsearch8',
    'elasticsearch_server' => [
        'hostname' => 'opensearch-cluster.example.com',
        'port' => '9200',
        'index' => 'magento2_large',
        'enable_auth' => false,
        'timeout' => 30  // Increased for large catalogs
    ]
],

Search Performance Tuning

{
  "query": {
    "bool": {
      "must": [
        { "match": { "name": "widget" } }
      ],
      "filter": [
        { "term": { "status": 1 } },
        { "term": { "visibility": [2, 4] } },
        { "range": { "price": { "gte": 10, "lte": 100 } } }
      ]
    }
  },
  "aggs": {
    "categories": { "terms": { "field": "category_ids", "size": 20 } },
    "price_ranges": { "histogram": { "field": "price", "interval": 10 } }
  }
}

Search Optimization Checklist

1. Use filters before full-text queries
2. Limit aggregations to needed fields
3. Set appropriate shard count (10-50GB per shard)
4. Use routing for category-based queries
5. Cache frequent search results
6. Monitor query latency and optimize

Quiz

1. At what catalog size should you consider full architecture review?

Question 1 options

2. What indexing mode is best for large catalogs?

Question 2 options

3. How to optimize EAV for large catalogs?

Question 3 options

Flashcards

Question

Large catalog threshold?

Click to reveal answer

Answer

>1M products requires full architecture review

Question

EAV optimization for large catalogs?

Click to reveal answer

Answer

Flat tables, selective attribute loading, table partitioning

Question

Indexing mode for large catalogs?

Click to reveal answer

Answer

Scheduled indexing to reduce save-time overhead

Question

Search optimization for large catalogs?

Click to reveal answer

Answer

Filters before full-text, limit aggregations, cache results

Revision Notes

Key Takeaways

  • 1. Catalogs >1M products need full architecture review
  • 2. Flat tables and selective attribute loading optimize EAV
  • 3. Scheduled indexing reduces save-time overhead for large catalogs
  • 4. Search optimization: filters first, limit aggregations, cache
  • 5. Monitor reindex duration and optimize batch sizes

Interview Tips

  • • Discuss performance challenges at different catalog sizes
  • • Explain EAV optimization strategies for large catalogs
  • • Compare realtime vs scheduled indexing trade-offs

Cheat Sheet

Large Catalog Optimization:
  >1M products: Full architecture review
  EAV: Flat tables + selective attributes
  Indexing: Scheduled mode

Search:
  Filters before full-text
  Limit aggregations
  Cache frequent queries

Performance:
  Monitor reindex duration
  Batch product saves
  Use cursor-based pagination