Covering Indexes praktisch nutzen

Was ist ein Covering Index?

Ein Covering Index ist ein Index, der sämtliche Spalten enthält, die eine SQL-Abfrage benötigt — sowohl die Spalten in der WHERE-Klausel als auch jene im SELECT-Teil und in der ORDER BY-Klausel. Wenn MySQL einen solchen Covering Index findet, kann es die Abfrage vollständig aus dem Index beantworten, ohne einen einzigen Blick in die eigentliche Tabelle (den sogenannten Heap) zu werfen. Dieser Index-Only-Scan ist einer der schnellsten Ausführungsmodi in MySQL und InnoDB.

Der Begriff "Covering Index" beschreibt kein spezifisches MySQL-Feature, sondern eine Eigenschaft: Der Index "deckt" alle Informationsanforderungen der Abfrage ab. In InnoDB ist jeder Secondary Index bereits ein erweiterter Index, der implizit den Primary Key enthält — das erleichtert es erheblich, Covering Indexes zu bauen. Sobald dem Index eine Spalte hinzugefügt wird, die sonst per Heap-Fetch aus der Zeile geholt werden müsste, wird der Index zum Covering Index für diese Query.

Covering Indexes sind besonders wertvoll für Abfragen, die häufig auf großen Tabellen ausgeführt werden, aber nur wenige Spalten benötigen — typisch für Magento-Listings, Admin-Grid-Abfragen und Dashboard-Reports. Mit einem gut platzierten Covering Index können solche Abfragen von mehreren Sekunden auf Millisekunden fallen, weil MySQL nur die kompakte Index-Struktur liest statt breiter, unkomprimierter Tabellenzeilen.

EXPLAIN und "Using index" erkennen

Der verlässlichste Weg zu erkennen, ob MySQL einen Covering Index verwendet, ist EXPLAIN. In der Spalte "Extra" erscheint dann der Eintrag "Using index" — und nur dieser Eintrag. Sobald dort "Using where" ohne "Using index" steht oder gar nichts, wird der Heap benutzt.

Folgendes Beispiel zeigt einen einfachen Test auf der Magento-Tabelle customer_entity. Ohne Covering Index muss MySQL für jede Zeile, die den WHERE-Filter erfüllt, die vollständige Tabellenzeile nachladen:

-- Query without covering index: MySQL must do a heap fetch for email and status
EXPLAIN
SELECT id, email, status
FROM customer_entity
WHERE website_id = 1
  AND is_active = 1
ORDER BY created_at;

-- Typical EXPLAIN output WITHOUT covering index:
-- +----+-------------+-----------------+------+---------------+------------+---------+-------+------+-----------------------------+
-- | id | select_type | table           | type | possible_keys | key        | key_len | ref   | rows | Extra                       |
-- +----+-------------+-----------------+------+---------------+------------+---------+-------+------+-----------------------------+
-- |  1 | SIMPLE      | customer_entity | ref  | idx_website   | idx_website| 4       | const | 5842 | Using index condition       |
-- +----+-------------+-----------------+------+---------------+------------+---------+-------+------+-----------------------------+
-- "Using index condition" means MySQL still reads table rows for email/status columns
  

Nachdem der passende Covering Index angelegt wurde, sieht EXPLAIN ganz anders aus:

-- Create a covering index: WHERE columns first, then ORDER BY column, then SELECT columns
CREATE INDEX idx_cov_website_active
  ON customer_entity (website_id, is_active, created_at, email, status, entity_id);

-- Same query after index creation:
EXPLAIN
SELECT entity_id, email, status
FROM customer_entity
WHERE website_id = 1
  AND is_active = 1
ORDER BY created_at;

-- EXPLAIN output WITH covering index:
-- +----+-------------+-----------------+------+-------------------------------+----------------------+---------+-------------+------+-------------+
-- | id | select_type | table           | type | possible_keys                 | key                  | key_len | ref         | rows | Extra       |
-- +----+-------------+-----------------+------+-------------------------------+----------------------+---------+-------------+------+-------------+
-- |  1 | SIMPLE      | customer_entity | ref  | idx_cov_website_active        | idx_cov_website_active | 10    | const,const | 5842 | Using index |
-- +----+-------------+-----------------+------+-------------------------------+----------------------+---------+-------------+------+-------------+
-- "Using index" = pure index-only scan, no heap fetch at all
  

Der entscheidende Unterschied: "Using index" bedeutet, dass MySQL ausschließlich im Index-Baum liest. "Using index condition" (Index Condition Pushdown) bedeutet dagegen, dass die Filterbedingung im Index ausgewertet wird, aber für die zurückgegebenen Zeilen trotzdem ein Heap-Fetch stattfindet, um alle SELECT-Spalten zu holen.

Für eine noch detailliertere Analyse empfiehlt sich EXPLAIN FORMAT=JSON, das den vollständigen Zugriffsplan mit Kosten-Schätzungen ausgibt und für jede Tabelle explizit "using_index": true zeigt.

Composite Index Design: Spaltenreihenfolge für Covering Indexes

Die Reihenfolge der Spalten in einem Composite Index entscheidet darüber, ob MySQL den Index überhaupt nutzen kann und wie effizient der Scan wird. Für Covering Indexes gilt eine klare Priorisierung: Erst die Equality-Spalten aus der WHERE-Klausel, dann Range- oder ORDER BY-Spalten, zuletzt die reinen SELECT-Spalten, die nur mitgeführt werden, damit kein Heap-Fetch nötig ist.

Das Muster lautet kurz: WHERE-Equality → ORDER BY → SELECT-Payload. Diese Reihenfolge ist nicht beliebig — MySQL kann einen Composite Index von links nach rechts nutzen (Leftmost Prefix Rule), aber nicht "quer durchspringen". Eine Spalte weiter rechts im Index ist nur nutzbar, wenn alle Spalten links davon entweder durch einen Equality-Filter abgedeckt sind oder als Payload ohne Filterung mitgetragen werden.

-- Example: Find active customers on website 1, sorted by signup date
-- Needed columns: website_id (WHERE =), is_active (WHERE =), created_at (ORDER BY),
--                 email (SELECT), status (SELECT), entity_id (SELECT/PK)

-- WRONG order: putting payload columns before ORDER BY column breaks sort optimization
-- CREATE INDEX idx_bad ON customer_entity (website_id, email, status, is_active, created_at);

-- CORRECT order for covering index:
-- 1. Equality filters (high selectivity first, but both are equality so order is flexible)
-- 2. ORDER BY column (must be directly after the equality filters)
-- 3. SELECT payload columns (no filter needed, just carried along)
CREATE INDEX idx_cov_customer_listing
  ON customer_entity (website_id, is_active, created_at, email, status, entity_id);

-- Verify the index is used as covering:
EXPLAIN
SELECT entity_id, email, status
FROM customer_entity
WHERE website_id = 1
  AND is_active = 1
ORDER BY created_at
LIMIT 100;
-- Extra: Using index  <-- this is what we want
  

Ein häufiger Fehler ist, die Spalten aus dem SELECT-Teil vor der ORDER BY-Spalte im Index zu platzieren. Das unterbricht die Nutzbarkeit des Index für die Sortierung und zwingt MySQL, ein filesort durchzuführen — was EXPLAIN mit "Using filesort" anzeigt.

Heap Fetch: Warum Tabellen-Lookups teuer sind

In InnoDB ist der Clustered Index (Primary Key) der eigentliche Speicherort der Tabellendaten. Jeder Secondary Index enthält nur die indizierten Spalten und den Primary Key als Zeiger. Wenn MySQL über einen Secondary Index eine Zeile findet, muss es anschließend den Primary Key nutzen, um die vollständige Zeile aus dem Clustered Index zu holen — das ist der Heap Fetch, auch "double lookup" genannt.

Bei einer Abfrage, die 10.000 Zeilen zurückgibt, bedeutet das ohne Covering Index: 10.000 Index-Lookups im Secondary Index plus 10.000 weitere Lookups im Clustered Index. Jeder dieser Lookups bedeutet potenziell ein anderes B-Tree-Blatt — also zufällige I/O-Operationen statt sequentiellem Lesen. Bei einem Covering Index hingegen liest MySQL nur im Secondary Index und benötigt den Clustered Index gar nicht.

-- Inspect existing indexes and their sizes to understand overhead
SHOW INDEX FROM catalog_product_entity\G

-- Check index sizes via information_schema
SELECT
  index_name,
  COUNT(*)                        AS index_columns,
  GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.STATISTICS
WHERE table_schema = 'magento'
  AND table_name   = 'catalog_product_entity'
GROUP BY index_name
ORDER BY index_name;

-- Compare data_length vs index_length to understand index overhead
SELECT
  table_name,
  ROUND(data_length / 1024 / 1024, 1)       AS data_mb,
  ROUND(index_length / 1024 / 1024, 1)      AS index_mb,
  ROUND(index_length / data_length * 100, 0) AS index_pct
FROM information_schema.TABLES
WHERE table_schema = 'magento'
  AND table_name IN ('catalog_product_entity', 'catalog_product_index_price',
                     'customer_entity', 'sales_order')
ORDER BY data_mb DESC;
  

Die Ergebnisse dieser Abfrage zeigen oft, dass Index-Daten bereits 30–60 % der Tabellengröße ausmachen. Ein Covering Index erhöht diesen Anteil weiter — deshalb muss die Entscheidung für einen breiten Covering Index immer abgewogen werden.

Covering Index für Magento Katalog-Queries

Die Magento-Tabelle catalog_product_flat_1 (oder der Katalog-Index catalog_product_index_price für Preise) ist das Fundament für Kategorielistings. Für typische Listing-Abfragen — welche Produkte in einer Kategorie sind aktiv, sichtbar, auf welcher Website und zu welchem Preis — ist ein Covering Index der effektivste Optimierungsansatz.

Der eigentliche Engpass in Magento-Katalog-Queries liegt häufig in der Verbindung zwischen dem Flat-Katalog und dem Preisindex. Für Abfragen, die Status, Visibility und Preis gleichzeitig brauchen, lohnt sich ein Covering Index direkt auf dem Preisindex.

-- Typical Magento product listing query pattern on price index
-- Columns needed: website_id (WHERE =), customer_group_id (WHERE =),
--                 entity_id (JOIN/SELECT), final_price (SELECT/ORDER BY)
EXPLAIN
SELECT entity_id, final_price, min_price
FROM catalog_product_index_price
WHERE website_id         = 1
  AND customer_group_id  = 0
ORDER BY final_price
LIMIT 24;

-- Create covering index for this access pattern
CREATE INDEX idx_cov_price_listing
  ON catalog_product_index_price
  (website_id, customer_group_id, final_price, min_price, entity_id);

-- For catalog_product_entity: covering index for status + visibility queries
-- (used by layered navigation and category page visibility checks)
CREATE INDEX idx_cov_product_status
  ON catalog_product_entity
  (entity_type_id, attribute_set_id, entity_id);

-- Verify with EXPLAIN ANALYZE (MySQL 8.0+): shows actual rows and timing
EXPLAIN ANALYZE
SELECT cppe.entity_id, cppe.final_price
FROM catalog_product_index_price cppe
WHERE cppe.website_id        = 1
  AND cppe.customer_group_id = 0
ORDER BY cppe.final_price
LIMIT 24;
-- Look for "Index Only Scan" or "(actual time=...)" with very low row access
  

Covering Index für Bestelllisten und customer_entity

Das Magento Admin-Grid für Bestellungen führt häufig Abfragen auf sales_order durch, die nach Datum gefiltert und sortiert werden und dabei nur wenige Spalten anzeigen — ein klassischer Anwendungsfall für Covering Indexes. Ohne einen solchen Index liest MySQL für jede gefilterte Bestellung die vollständige Zeile aus dem Clustered Index.

-- Common Magento sales order list query (admin grid pattern)
-- Filter: store_id + status + date range; Select: increment_id, grand_total, customer_email
EXPLAIN
SELECT entity_id, increment_id, status, grand_total, customer_email
FROM sales_order
WHERE store_id   = 1
  AND status     = 'processing'
  AND created_at >= '2025-01-01'
  AND created_at  < '2025-02-01'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Covering index for this pattern:
-- Equality columns first: store_id, status
-- Range + ORDER BY column: created_at (MySQL can use this for range scan + sort together)
-- Payload columns: increment_id, grand_total, customer_email, entity_id
CREATE INDEX idx_cov_order_grid
  ON sales_order (store_id, status, created_at, increment_id, grand_total,
                  customer_email, entity_id);

-- After index creation, confirm "Using index" in EXPLAIN
EXPLAIN
SELECT entity_id, increment_id, status, grand_total, customer_email
FROM sales_order
WHERE store_id   = 1
  AND status     = 'processing'
  AND created_at >= '2025-01-01'
  AND created_at  < '2025-02-01'
ORDER BY created_at DESC
LIMIT 20;
-- Expected Extra: Using where; Using index
  

Wichtig: "Using where; Using index" bedeutet, dass MySQL ausschließlich im Index liest (kein Heap Fetch) und dabei noch WHERE-Bedingungen gegen die Index-Werte prüft. Das ist vollständig korrekt für Covering Indexes mit Range-Filtern — MySQL liest den Index-Bereich und filtert dabei, ohne die Tabelle zu berühren.

Wann kein Covering Index hilft: breite Indizes und Write-Overhead

Covering Indexes sind kein universelles Allheilmittel. Es gibt Situationen, in denen sie mehr schaden als nützen, und ein guter Datenbankentwickler muss diese Grenzen kennen.

Erstens: Breite Indizes auf Spalten mit großem Datenvolumen. Ein Covering Index auf sales_order, der auch customer_note (TEXT) oder ähnliche breite Spalten enthält, ist kontraproduktiv. TEXT- und BLOB-Spalten können nicht vollständig in InnoDB-Indizes aufgenommen werden — MySQL würde den Index gar nicht als Covering Index verwenden.

Zweitens: Sehr write-heavy Tabellen. Jeder INSERT, UPDATE und DELETE muss alle Indizes der Tabelle pflegen. Eine Tabelle wie cataloginventory_stock_item, auf der bei jedem Bestellvorgang Mengen aktualisiert werden, verträgt keine zusätzlichen breiten Indizes gut. Der Write-Overhead überwiegt den Read-Vorteil.

Drittens: Spalten mit sehr geringer Selektivität als führende Index-Spalten. Ein Index, der mit is_active (nur Werte 0 und 1) beginnt, wird von MySQL oft zugunsten eines Full Table Scans ignoriert, weil die Selektivität zu gering ist, um den Index-Traversal zu rechtfertigen. In solchen Fällen hilft kein Covering Index, wenn die WHERE-Spalten selbst keine ausreichende Selektivität haben.

Invisible Indexes in MySQL 8: sicher testen

MySQL 8.0 führte das Konzept der Invisible Indexes ein — ein Werkzeug, das das sichere Testen von Index-Änderungen in der Produktion ermöglicht, ohne den Index physisch löschen zu müssen. Ein Invisible Index wird von MySQL nicht für die Query-Optimierung genutzt, bleibt aber strukturell intakt und wird bei DML-Operationen weiterhin gepflegt.

Das Vorgehen für einen sicheren Test: Den neuen Covering Index anlegen, zunächst als invisible, dann mit EXPLAIN prüfen (mit /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */), und erst nach Bestätigung als visible schalten. Wenn sich der Index als problematisch herausstellt, kann er wieder auf invisible gesetzt werden, statt ihn sofort zu löschen — ein DROP und neu-CREATE auf einer großen Tabelle ist teuer.

-- Create a new covering index as INVISIBLE first (MySQL 8.0+)
-- This maintains the index but the optimizer ignores it by default
CREATE INDEX idx_cov_test
  ON customer_entity (website_id, is_active, created_at, email, entity_id)
  INVISIBLE;

-- Test if the index would help by enabling invisible index usage for this session
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

EXPLAIN
SELECT entity_id, email
FROM customer_entity
WHERE website_id = 1
  AND is_active  = 1
ORDER BY created_at
LIMIT 50;
-- If Extra shows "Using index", the covering index works as expected

-- Make the index visible for production use
ALTER TABLE customer_entity ALTER INDEX idx_cov_test VISIBLE;

-- If problems arise later: make it invisible again (no DROP needed)
-- ALTER TABLE customer_entity ALTER INDEX idx_cov_test INVISIBLE;

-- Check visibility status of all indexes
SELECT index_name, is_visible
FROM information_schema.STATISTICS
WHERE table_schema = 'magento'
  AND table_name   = 'customer_entity'
GROUP BY index_name, is_visible;
  

Index-Only-Scan messen und verifizieren

Das bloße Erscheinen von "Using index" in EXPLAIN zeigt an, dass ein Covering Index genutzt wird — aber die tatsächliche Auswirkung auf die Performance lässt sich nur durch Messung belegen. MySQL bietet mehrere Methoden, um den Unterschied zwischen Heap-Fetch und Index-Only-Scan zu quantifizieren.

Die direkteste Methode ist ein Vergleich von Handler-Statistiken vor und nach der Einführung des Index. Die Variable Handler_read_rnd_next zählt Zeilenlese-Operationen aus dem Clustered Index. Wenn dieser Wert nach Einführung des Covering Index auf nahezu null sinkt, ist der Beweis erbracht.

-- Measure handler reads before and after covering index
-- Reset status counters for this session
FLUSH STATUS;

-- Run the query WITHOUT covering index (first, drop or make invisible the covering index)
SELECT entity_id, email, status
FROM customer_entity
WHERE website_id = 1
  AND is_active  = 1
ORDER BY created_at
LIMIT 1000;

-- Check how many table rows were read
SHOW SESSION STATUS LIKE 'Handler_read%';
-- Handler_read_rnd_next shows rows read from table (high = heap fetches happening)
-- Handler_read_key    shows index lookups
-- Handler_read_next   shows sequential index reads

-- Now enable the covering index and repeat
FLUSH STATUS;

SELECT entity_id, email, status
FROM customer_entity
WHERE website_id = 1
  AND is_active  = 1
ORDER BY created_at
LIMIT 1000;

SHOW SESSION STATUS LIKE 'Handler_read%';
-- After covering index: Handler_read_rnd_next should be 0 or near 0
-- Handler_read_next should cover all 1000 rows (pure index reads)
  

Ein weiterer zuverlässiger Indikator ist EXPLAIN ANALYZE in MySQL 8.0, das die tatsächlich gelesenen Zeilen und die Ausführungszeit zeigt. Bei einem Index-Only-Scan auf 10.000 Zeilen sind Ausführungszeiten unter 1 ms typisch; mit Heap Fetch kann dieselbe Abfrage 50–200 ms benötigen, abhängig vom Buffer Pool Hit Ratio.

Zusammenfassung

Ein Covering Index ist einer der wirkungsvollsten Optimierungshebel in MySQL und InnoDB. Indem der Index alle Spalten enthält, die eine Abfrage benötigt, eliminiert er den teuren Heap Fetch vollständig. EXPLAIN zeigt den Erfolg mit "Using index" in der Extra-Spalte. Die Spaltenreihenfolge folgt dem Muster Equality-Filter → ORDER BY → SELECT-Payload. Für Magento bieten Katalog-Listings, Bestellgrids und Customer-Listings die höchste Rendite für Covering-Index-Investitionen.

FAQ: Covering Indexes