Magento Indexer und SQL: Welche Tabellen sich warum verändern

Warum das Magento Indexer-System verstehen?

Der Magento Indexer ist einer der komplexesten und gleichzeitig am meisten missverstandenen Teile des Systems. Wenn Produkte im Frontend nicht aktuell sind, Preise nicht stimmen oder Produktspeicherungen eine Minute dauern, liegt die Ursache fast immer im Indexer. Um solche Probleme zu diagnostizieren und zu beheben, muss man verstehen, was auf Datenbankebene passiert: Welche Tabellen werden wann befüllt? Was schreiben Trigger? Wie misst man, wie viele Produkte noch neu indexiert werden müssen?

Dieser Artikel erklärt den Magento Indexer SQL-Mechanismus von Grund auf: von indexer_state über Changelog-Tabellen bis hin zu konkreten Diagnose-Abfragen, mit denen man den Zustand des Indexers in Sekunden beurteilen kann.

1. indexer_state: Status und Modus

Die Tabelle indexer_state ist die Schaltzentrale des Magento Indexer-Systems. Jede Zeile repräsentiert einen Indexer mit seinem aktuellen Status (status) und Betriebsmodus (mode).

-- Aktuellen Status aller Indexer
SELECT
    indexer_id,
    status,         -- 'valid', 'invalid', 'working'
    mode,           -- 'realtime' (on save) oder 'schedule' (cron)
    updated         -- letzter Statuswechsel
FROM indexer_state
ORDER BY indexer_id;

-- Nur invalide Indexer anzeigen (Handlungsbedarf)
SELECT indexer_id, status, mode, updated
FROM indexer_state
WHERE status = 'invalid'
ORDER BY updated DESC;

Die möglichen Statuswerte bedeuten:

• valid: Der Index ist aktuell und konsistent mit den Quelldaten.
• invalid: Daten haben sich geändert und der Index muss neu aufgebaut werden.
• working: Ein Reindex-Prozess läuft gerade. Bleibt dieser Status stecken, ist der Prozess abgestürzt.

-- Indexer-Status manuell auf 'invalid' setzen (entspricht bin/magento indexer:reset)
UPDATE indexer_state
SET status = 'invalid'
WHERE indexer_id = 'catalog_product_price';

-- Indexer-Modus auf Schedule umstellen (Update On Schedule)
UPDATE indexer_state
SET mode = 'schedule'
WHERE indexer_id IN ('catalog_product_price', 'catalog_product_flat');
-- Danach: bin/magento cache:flush

2. mview_state: Materialized View Tracking

Im Schedule-Modus verwendet Magento das Konzept der Materialized Views (Mviews). Die Tabelle mview_state verwaltet den Fortschritt dieser Views. Die Schlüsselspalte ist version_id: Sie gibt an, bis zu welcher Changelog-Version bereits verarbeitet wurde.

-- Alle Mview-Zustände mit Version und Status
SELECT
    state_id,
    view_id,           -- z.B. 'catalog_product_flat', 'cataloginventory_stock'
    mode,              -- 'enabled' oder 'disabled'
    status,            -- 'idle', 'working', 'suspended'
    updated,
    version_id         -- bis zu dieser Version wurde der Changelog verarbeitet
FROM mview_state
ORDER BY view_id;

Die version_id in mview_state wird mit dem maximalen version_id aus der jeweiligen Changelog-Tabelle verglichen. Ist der Wert in der Changelog-Tabelle höher, gibt es unverarbeitete Änderungen.

3. Changelog-Tabellen und Trigger

Changelog-Tabellen sind der Kern des Delta-Indexierungsverfahrens. Jede relevante EAV-Tabelle hat einen MySQL-Trigger, der bei Änderungen die betroffene entity_id in die zugehörige Changelog-Tabelle schreibt. Die Changelog-Tabellen haben das Suffix _cl.

-- Alle Changelog-Tabellen im System (Namenskonvention: *_cl)
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    CREATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME LIKE '%_cl'
ORDER BY TABLE_NAME;

-- Neueste Einträge im Preisindex-Changelog
SELECT
    version_id,
    entity_id      -- Produkt-ID die geändert wurde
FROM catalog_product_index_price_cl
ORDER BY version_id DESC
LIMIT 20;

-- Alle Trigger auf EAV-Tabellen anzeigen
SELECT
    TRIGGER_NAME,
    EVENT_OBJECT_TABLE,
    EVENT_MANIPULATION,   -- INSERT, UPDATE, DELETE
    ACTION_TIMING,        -- BEFORE oder AFTER
    ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
  AND EVENT_OBJECT_TABLE LIKE 'catalog_product_entity%'
ORDER BY EVENT_OBJECT_TABLE, TRIGGER_NAME;

4. Pending-Reindex-Count ermitteln

Die wichtigste Diagnose-Abfrage: Wie viele Produkte warten noch auf Neuindexierung? Dies ermittelt man durch Vergleich der version_id im Changelog mit der in mview_state gespeicherten letzten verarbeiteten Version.

-- Anzahl ausstehender Reindex-Einträge für den Flat-Produktindex
SELECT
    COUNT(*) AS pending_entries
FROM catalog_product_flat_cl
WHERE version_id > (
    SELECT COALESCE(version_id, 0)
    FROM mview_state
    WHERE view_id = 'catalog_product_flat'
);

-- Ausstehende Einträge für alle wichtigen Changelog-Tabellen auf einmal
SELECT 'catalog_product_flat'          AS view_id,
       COUNT(*)                         AS pending
FROM catalog_product_flat_cl
WHERE version_id > (SELECT COALESCE(MAX(version_id),0) FROM mview_state WHERE view_id='catalog_product_flat')
UNION ALL
SELECT 'catalog_product_price',
       COUNT(*)
FROM catalog_product_index_price_cl
WHERE version_id > (SELECT COALESCE(MAX(version_id),0) FROM mview_state WHERE view_id='catalog_product_price')
UNION ALL
SELECT 'cataloginventory_stock',
       COUNT(*)
FROM cataloginventory_stock_status_cl
WHERE version_id > (SELECT COALESCE(MAX(version_id),0) FROM mview_state WHERE view_id='cataloginventory_stock')
ORDER BY pending DESC;

5. Preisindex: catalog_product_index_price

Der flache Preisindex catalog_product_index_price ist die Grundlage für alle Preisanzeigen im Frontend. Er enthält für jede Kombination aus Produkt, Kundengruppe und Website den berechneten Endpreis.

-- Preisindex für ein bestimmtes Produkt
SELECT
    entity_id,
    customer_group_id,
    website_id,
    tax_class_id,
    price,             -- Originalpreis
    final_price,       -- Preis nach Rabatten
    min_price,         -- niedrigster Preis (bei konfigurierbaren Produkten)
    max_price,         -- höchster Preis
    tier_price         -- Staffelpreis (NULL wenn nicht vorhanden)
FROM catalog_product_index_price
WHERE entity_id = 42
ORDER BY customer_group_id, website_id;

-- Produkte mit ungewöhnlich hohen Preis-Unterschieden (Angebotspreis-Check)
SELECT
    entity_id,
    website_id,
    price,
    final_price,
    ROUND((1 - final_price/price) * 100, 1) AS discount_percent
FROM catalog_product_index_price
WHERE customer_group_id = 0      -- Nicht-eingeloggte Kunden
  AND price > 0
  AND final_price < price        -- nur wenn Rabatt vorhanden
  AND (1 - final_price/price) > 0.5  -- mehr als 50% Rabatt
ORDER BY discount_percent DESC
LIMIT 20;

6. Kategorieindex: catalog_category_product_index

Der Kategorieindex catalog_category_product_index speichert die vollständige Produkt-Kategorie-Zuordnung nach der Indexierung, inklusive vererbter Zuordnungen aus Eltern-Kategorien. Er ist die Grundlage für alle Kategorie-Listings im Frontend.

-- Alle Kategorien eines Produkts (aus dem Index)
SELECT
    ccpi.category_id,
    ccpi.product_id,
    ccpi.position,
    ccpi.is_parent,    -- 1 = direkte Zuordnung, 0 = vererbt
    ccpi.store_id,
    ccpi.visibility
FROM catalog_category_product_index_store1 ccpi
-- Hinweis: Bei MSI-Shops heißt die Tabelle catalog_category_product_index_store{id}
WHERE ccpi.product_id = 42
ORDER BY ccpi.category_id;

-- Rohdaten vs. Index: Direkte Zuordnungen aus der Quell-Tabelle
SELECT
    category_id,
    product_id,
    position
FROM catalog_category_product
WHERE product_id = 42
ORDER BY category_id;
-- Der Index enthält zusätzlich vererbte Zuordnungen aus Eltern-Kategorien

7. Warum Produktspeicherungen langsam sind

Ein häufiges Performance-Problem in Magento ist, dass das Speichern eines Produkts im Admin 30 Sekunden oder länger dauert. Die Ursache liegt fast immer in der Kombination aus EAV-Triggern und Realtime-Indexierung.

Bei jedem Produkt-Save passiert Folgendes in der Datenbank:

1. Schreiboperationen auf mehrere EAV-Tabellen (catalog_product_entity_varchar, _int, _decimal, _text, _datetime)
2. Jede Schreiboperation triggert MySQL-Trigger, die Einträge in alle relevanten Changelog-Tabellen schreiben
3. Im Realtime-Modus: Sofortiger Rebuild aller invaliden Indexer
4. Cache-Invalidierung für das betroffene Produkt

-- Anzahl Trigger auf Produkt-EAV-Tabellen ermitteln
SELECT COUNT(*) AS trigger_count
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
  AND EVENT_OBJECT_TABLE LIKE 'catalog_product_entity%';

-- Welche Changelog-Tabellen bei einem Produkt-Save beschrieben werden
SELECT DISTINCT
    SUBSTRING_INDEX(ACTION_STATEMENT, '`', 2) AS target_table_hint,
    EVENT_OBJECT_TABLE,
    TRIGGER_NAME
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
  AND EVENT_OBJECT_TABLE LIKE 'catalog_product_entity%'
ORDER BY EVENT_OBJECT_TABLE;

Die Lösung: Alle Indexer auf Update on Schedule umstellen. Dann werden Trigger zwar weiterhin in die Changelog-Tabellen schreiben, der eigentliche Reindex erfolgt aber durch den Cron-Job und belastet den Save-Vorgang nicht mehr.

-- Alle Indexer auf Schedule-Modus umstellen
UPDATE indexer_state SET mode = 'schedule';

-- Entspricht dem CLI-Befehl:
-- bin/magento indexer:set-mode schedule
-- Danach: bin/magento cache:flush

8. Komplette Indexer-Diagnose per SQL

Die folgende Abfrage gibt einen vollständigen Überblick über den Indexer-Zustand, kombiniert mit den ausstehenden Changelog-Einträgen:

-- Vollständige Indexer-Übersicht
SELECT
    ist.indexer_id,
    ist.status,
    ist.mode,
    ist.updated                          AS last_updated,
    ms.status                            AS mview_status,
    ms.version_id                        AS processed_version
FROM indexer_state ist
LEFT JOIN mview_state ms ON ms.view_id = ist.indexer_id
ORDER BY
    FIELD(ist.status, 'invalid', 'working', 'valid'),
    ist.indexer_id;

-- Wachstum der Changelog-Tabellen überwachen (für Monitoring)
SELECT
    t.TABLE_NAME,
    t.TABLE_ROWS                         AS approx_rows,
    ROUND(t.DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    t.CREATE_TIME
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_NAME LIKE '%_cl'
ORDER BY t.TABLE_ROWS DESC;

-- Changelog-Tabellen bereinigen (nach vollständigem Reindex)
-- Magento tut das automatisch, aber bei hängenden Jobs kann manuell bereinigt werden
TRUNCATE catalog_product_flat_cl;
TRUNCATE catalog_product_index_price_cl;
TRUNCATE cataloginventory_stock_status_cl;
-- Danach immer vollständigen Reindex durchführen:
-- bin/magento indexer:reindex

9. Zusammenfassung

Das Magento Indexer-System auf SQL-Ebene zu verstehen bedeutet, drei Konzepte zu beherrschen: indexer_state für den Gesamtstatus, mview_state für den Verarbeitungsfortschritt und Changelog-Tabellen als Kommunikationskanal zwischen EAV-Triggern und Indexer-Prozessen.

10. FAQ: Magento Indexer SQL