Index Tables in Magento

The EAV Performance Problem

Without indexes, listing products requires multiple JOINs:

-- Slow EAV query without indexes
SELECT p.entity_id, v.value AS name, d.value AS price
FROM catalog_product_entity p
LEFT JOIN catalog_product_entity_varchar v ON v.entity_id = p.entity_id AND v.attribute_id = 71
LEFT JOIN catalog_product_entity_decimal d ON d.entity_id = p.entity_id AND d.attribute_id = 75
WHERE v.value LIKE '%T-Shirt%'
ORDER BY d.value ASC;
-- 500ms+ for 10,000 products

Index Solution

Index tables pre-compute EAV data into flat structures:

-- Fast index query
SELECT entity_id, name, price, final_price
FROM catalog_product_index_price
WHERE name LIKE '%T-Shirt%'
ORDER BY price ASC;
-- 5ms for 10,000 products

Index Types

catalog_product_index_price

Stores computed prices for all customer groups and websites:

CREATE TABLE catalog_product_index_price (
    entity_id INT UNSIGNED NOT NULL COMMENT 'Entity ID',
    customer_group_id SMALLINT UNSIGNED NOT NULL COMMENT 'Customer Group ID',
    website_id SMALLINT UNSIGNED NOT NULL COMMENT 'Website ID',
    tax_class_id SMALLINT UNSIGNED DEFAULT NULL COMMENT 'Tax Class ID',
    orig_price DECIMAL(12,4) DEFAULT NULL COMMENT 'Original Price',
    price DECIMAL(12,4) DEFAULT NULL COMMENT 'Price',
    min_price DECIMAL(12,4) DEFAULT NULL COMMENT 'Min Price',
    max_price DECIMAL(12,4) DEFAULT NULL COMMENT 'Max Price',
    tier_price DECIMAL(12,4) DEFAULT NULL COMMENT 'Tier Price',
    tier_price_incl_tax DECIMAL(12,4) DEFAULT NULL COMMENT 'Tier Price Incl Tax',
    base_tier_price DECIMAL(12,4) DEFAULT NULL COMMENT 'Base Tier Price',
    base_tier_price_incl_tax DECIMAL(12,4) DEFAULT NULL COMMENT 'Base Tier Price Incl Tax',
    PRIMARY KEY (entity_id, customer_group_id, website_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Catalog Product Price Index';

Querying Prices

-- Get price for specific customer group and website
SELECT entity_id, price, min_price, max_price, tier_price
FROM catalog_product_index_price
WHERE entity_id = 123 AND customer_group_id = 0 AND website_id = 1;

-- Get all products sorted by price
SELECT entity_id, price
FROM catalog_product_index_price
WHERE customer_group_id = 0 AND website_id = 1
ORDER BY price ASC
LIMIT 20;

-- Products in price range
SELECT entity_id, price
FROM catalog_product_index_price
WHERE customer_group_id = 0 
  AND website_id = 1 
  AND price BETWEEN 10.00 AND 50.00;

Price Index with Tax

-- catalog_product_index_price_idx includes tax calculations
SELECT 
    p.entity_id,
    p.price,
    t.tax_rate,
    p.price * (1 + t.tax_rate) AS price_incl_tax
FROM catalog_product_index_price p
JOIN tax_class taxes ON taxes.class_id = p.tax_class_id
JOIN tax_rates t ON t.tax_class_id = taxes.class_id
WHERE p.entity_id = 123 AND p.website_id = 1;

catalog_product_index_eav

Flattens EAV attribute values into index:

CREATE TABLE catalog_product_index_eav (
    entity_id INT UNSIGNED NOT NULL COMMENT 'Entity ID',
    attribute_id SMALLINT UNSIGNED NOT NULL COMMENT 'Attribute ID',
    store_id SMALLINT UNSIGNED NOT NULL COMMENT 'Store ID',
    value INT UNSIGNED DEFAULT NULL COMMENT 'Value',
    PRIMARY KEY (entity_id, attribute_id, store_id),
    INDEX IDX_VALUE (value),
    INDEX IDX_STORE_ID (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Catalog Product EAV Index';

Category Product Index

-- Maps products to categories with positions
CREATE TABLE catalog_category_product_index (
    category_id INT UNSIGNED NOT NULL COMMENT 'Category ID',
    position INT DEFAULT NULL COMMENT 'Position',
    is_parent SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Parent',
    entity_id INT UNSIGNED NOT NULL COMMENT 'Entity ID',
    store_id SMALLINT UNSIGNED NOT NULL COMMENT 'Store ID',
    PRIMARY KEY (category_id, entity_id, store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Catalog Category Product Index';

-- Query products in category
SELECT entity_id, position
FROM catalog_category_product_index
WHERE category_id = 3 AND store_id = 1
ORDER BY position;

+-----------+----------+
| entity_id | position |
+-----------+----------+
|        10 |        1 |
|        15 |        2 |
|        20 |        3 |
+-----------+----------+

Index Status Check

# Check all index statuses
bin/magento indexer:status

# Output:
+----------------------+---------+----------+-------------------+
| Title                | Status  | Update   | Schedule          |
+----------------------+---------+----------+-------------------+
| Product Prices       | invalid | Updated  | ---               |
| Category Products    | invalid | Updated  | ---               |
| Product Flat Data    | invalid | Updated  | ---               |
| Catalog Search       | invalid | Updated  | ---               |
+----------------------+---------+----------+-------------------+

Reindex Commands

# Reindex all
bin/magento indexer:reindex

# Reindex specific index
bin/magento indexer:reindex catalog_product_price
bin/magento indexer:reindex catalog_category_product
bin/magento indexer:reindex catalogsearch_fulltext

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

# Force reindex even if up to date
bin/magento indexer:reindex --force

Indexer Configuration

<!-- app/code/Vendor/Module/etc/indexer.xml -->
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/etc/indexer.xsd">
    <indexer id="catalog_product_price" 
             view_id="catalog_product_price"
             class="Magento\Catalog\Model\Indexer\Product\Price" 
             shared="0">
        <title translate="true">Product Prices</title>
        <description translate="true">Rebuild Product Prices Index</description>
    </indexer>
</config>

Batch Indexing for Large Catalogs

// Custom batch indexer
$batchSize = 1000;
$collection = $productCollection->create()->load();

$batch = [];
foreach ($collection as $product) {
    $batch[] = $product->getId();
    if (count($batch) >= $batchSize) {
        $this->reindexBatch($batch);
        $batch = [];
    }
}
if (!empty($batch)) {
    $this->reindexBatch($batch);
}

Index Performance Metrics

-- Monitor index table sizes
SELECT 
    table_name,
    table_rows,
    ROUND(data_length/1024/1024, 2) AS data_mb,
    ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE '%index%'
ORDER BY data_length DESC;

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