Magento 2 reindexing takes hours – MySQL deadlock and missing indexes

A shop with 50,000 products – full reindex took 6 hours and often ended with a deadlock error. Product prices and availability were updating with a delay. Fix time: 1 day.

Symptoms

• bin/magento indexer:reindex takes more than 4 hours
• MySQL error log: Deadlock found when trying to get lock; try restarting transaction
• Indexers stuck in “Processing” status for many hours
• Prices and stock levels out of date for customers

Diagnosis

bin/magento indexer:status
bin/magento indexer:show-mode

# Check for missing MySQL indexes
SELECT table_name, index_name, column_name
FROM information_schema.statistics
WHERE table_schema = 'magento'
AND table_name LIKE 'catalog_%'
ORDER BY table_name, index_name;

Cause

Two indexers ran in parallel and locked the same tables causing deadlocks. Additionally, missing MySQL indexes on EAV tables were slowing queries 10-fold.

Solution

# Switch to Update by Schedule mode
bin/magento indexer:set-mode schedule catalog_product_price
bin/magento indexer:set-mode schedule catalogsearch_fulltext
bin/magento indexer:set-mode schedule cataloginventory_stock

# Add missing MySQL indexes
ALTER TABLE catalog_product_entity_varchar
    ADD INDEX idx_attribute_store (attribute_id, store_id, value(50));

ALTER TABLE catalog_product_entity_decimal
    ADD INDEX idx_attribute_store (attribute_id, store_id);

# Increase lock wait timeout
# innodb_lock_wait_timeout = 120
# innodb_deadlock_detect = ON

Result

Full reindex time: from 6 hours to 18 minutes. Deadlocks: 0. Indexers in Schedule mode update data every minute without blocking the shop.