Indexers Introduction — What Indexers Are and Why They Exist

The Problem Indexers Solve

Without indexing, every product listing query would:

1. JOIN multiple EAV tables
2. Filter by attributes, categories, prices
3. Sort by relevancy, price, name
4. Calculate aggregates (count, sum)

This is slow for large catalogs.

How Indexers Work

Raw Data (EAV tables) -> Indexer -> Flat Table / Search Index

Indexers pre-compute and store results in flat tables optimized for reading.

Example: Catalog Product Flat

-- Without flat table (slow)
SELECT p.entity_id, eav_value.value
FROM catalog_product_entity p
JOIN catalog_product_entity_int eav_value ON ...
JOIN eav_attribute ON ...
WHERE eav_attribute.attribute_code = 'name'
AND ... (many more joins)

-- With flat table (fast)
SELECT * FROM catalog_product_flat_1
WHERE name LIKE '%widget%'

Index Performance Impact

All Indexers

php bin/magento indexer:info

Viewing Indexer Status

php bin/magento indexer:status

+------------------------------------+---------+-----------+-----------+
| Indexer                            | Status  | Update    | Schedule  |
+------------------------------------+---------+-----------+-----------+
| catalog_product_flat               | Ready   | Update    | Schedule  |
| catalog_category_product           | Ready   | Update    | Schedule  |
| catalog_product_price              | Ready   | Update    | Schedule  |
| catalogsearch_fulltext             | Ready   | Update    | Schedule  |
+------------------------------------+---------+-----------+-----------+

Indexer Information

# Detailed info about specific indexer
php bin/magento indexer:info catalog_product_price

# List all indexers
php bin/magento indexer:info

Indexer Interface

All indexers implement Magento\Framework\Indexer\ActionInterface:

interface ActionInterface
{
    public function executeFull();
    public function executeList(array $ids);
    public function executeRow($id);
}

Indexer Methods

Indexer State

-- Indexer state table
CREATE TABLE indexer_state (
    indexer_id VARCHAR(128),
    status VARCHAR(12), -- valid, invalid, working
    updated_at TIMESTAMP
);

Index Data Storage

-- Flat product table example
CREATE TABLE catalog_product_flat_1 (
    entity_id INT,
    name VARCHAR(255),
    price DECIMAL(12,2),
    sku VARCHAR(255),
    -- ... flattened attributes
);

Multi-Store Indexing

Each store view gets its own flat table:

catalog_product_flat_1 (store 1)
catalog_product_flat_2 (store 2)
catalog_product_flat_3 (store 3)

Indexer Modes

# Check current mode
php bin/magento indexer:show-mode

# Set to real-time (update on save)
php bin/magento indexer:set-mode realtime catalog_product_price

# Set to schedule (update by schedule)
php bin/magento indexer:set-mode schedule catalog_product_price

Indexer Status Commands

# Check status of all indexers
php bin/magento indexer:status

# Check specific indexer
php bin/magento indexer:status catalog_product_price

# Get detailed info
php bin/magento indexer:info

# Reset indexer state
php bin/magento indexer:reset catalog_product_price

Reindex Commands

# Reindex all
php bin/magento indexer:reindex

# Reindex specific
php bin/magento indexer:reindex catalog_product_price

# Reindex specific product
php bin/magento indexer:reindex catalog_product_price 123

Monitoring Indexer Health

-- Check index validity
SELECT indexer_id, status, updated_at
FROM indexer_state
WHERE status != 'valid';

-- Check last update time
SELECT indexer_id, updated_at
FROM indexer_state
ORDER BY updated_at;