SARGable Queries: Warum Funktionen auf Spalten teuer sind

1. Was bedeutet SARGable?

Der Begriff SARGable stammt aus dem Datenbankjargon und steht für Search ARGument able. Eine Query ist SARGable, wenn die Suchbedingung in der WHERE-Klausel so formuliert ist, dass der Query-Optimizer einen Index nutzen kann. Eine nicht-SARGable Query zwingt MySQL, jede einzelne Zeile der Tabelle zu lesen und die Bedingung zu evaluieren — also einen Full Table Scan durchzuführen.

Der entscheidende Unterschied: Ein Index speichert die Werte einer Spalte in sortierter Reihenfolge. MySQL kann darin binär suchen und einen Bereich effizient einschränken. Sobald jedoch eine Funktion auf die Spalte angewendet wird, ist der transformierte Wert nicht mehr im Index — MySQL muss für jede Zeile die Funktion auswerten und kann den Index nicht mehr als Sprungpunkt nutzen. Das gilt unabhängig davon, wie selektiv der Index wäre.

2. Warum Funktionen auf Spalten den Index deaktivieren

Wenn MySQL einen Index für eine Spalte created_at anlegt, speichert er die Original-Datetime-Werte sortiert. Bei der Query WHERE YEAR(created_at) = 2024 wird nicht nach dem Originalwert gesucht, sondern nach dem Ergebnis der Funktion YEAR(). Dieses Ergebnis steht nicht im Index. MySQL muss daher jede Zeile laden, YEAR() darauf anwenden und prüfen, ob das Ergebnis 2024 ergibt. Bei einer Million Zeilen bedeutet das eine Million Funktionsauswertungen.

-- NON-SARGable: function on indexed column, full table scan
EXPLAIN SELECT * FROM sales_order WHERE YEAR(created_at) = 2024;
-- type: ALL (full table scan), key: NULL

-- SARGable rewrite: range on original column, index can be used
EXPLAIN SELECT * FROM sales_order
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';
-- type: range, key: IDX_CREATED_AT (index range scan)

Die SARGable Variante formuliert dieselbe Bedingung ohne Funktion auf der Spalte. MySQL kann jetzt den Index für created_at nutzen und direkt zum Startpunkt des Datumsbereichs springen, ohne alle Zeilen zu scannen.

3. YEAR(created_at) — das häufigste Anti-Pattern

YEAR(created_at) ist vermutlich das am häufigsten anzutreffende nicht-SARGable Muster in MySQL-Anwendungen. Es sieht intuitiv aus, liest sich klar und liefert korrekte Ergebnisse — deaktiviert aber zuverlässig den Index auf created_at. Dasselbe gilt für MONTH(), DAY() und alle anderen Datumsfunktionen.

-- Pattern 1: YEAR() -- NON-SARGable
SELECT COUNT(*) FROM sales_order WHERE YEAR(created_at) = 2024;

-- SARGable rewrite:
SELECT COUNT(*) FROM sales_order
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- Pattern 2: MONTH() + YEAR() combination -- NON-SARGable
SELECT * FROM sales_order
WHERE YEAR(created_at) = 2024 AND MONTH(created_at) = 6;

-- SARGable rewrite:
SELECT * FROM sales_order
WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';

-- Pattern 3: DATE() function -- NON-SARGable
SELECT * FROM sales_order WHERE DATE(created_at) = '2024-06-15';

-- SARGable rewrite:
SELECT * FROM sales_order
WHERE created_at >= '2024-06-15 00:00:00'
  AND created_at <  '2024-06-16 00:00:00';

4. LOWER(email) und Case-insensitive Suche

LOWER(email) = 'test@example.com' ist ein klassischer Versuch, eine case-insensitive Suche zu erzwingen — und ein weiteres nicht-SARGable Muster. In modernen MySQL/MariaDB-Setups ist die Standard-Collation für VARCHAR-Spalten bereits utf8mb4_unicode_ci oder utf8mb4_general_ci, wobei _ci für Case Insensitive steht. Ein direkter Vergleich email = 'test@example.com' ist bei ci-Collation bereits case-insensitiv und nutzt den Index vollständig.

-- NON-SARGable: function on indexed email column
SELECT customer_id FROM customer_entity
WHERE LOWER(email) = 'test@example.com';
-- key: NULL, type: ALL

-- SARGable: direct comparison works if collation is _ci
-- (utf8mb4_unicode_ci is case-insensitive by default)
SELECT customer_id FROM customer_entity
WHERE email = 'test@example.com';
-- key: UNQ_CUSTOMER_ENTITY_EMAIL, type: ref

-- Check collation of a column:
SELECT COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'customer_entity'
  AND COLUMN_NAME = 'email';

Falls die Spalte eine _cs-Collation (Case Sensitive) hat und eine case-insensitive Suche tatsächlich nötig ist, ist der richtige Weg ein funktionaler Index (siehe Abschnitt 6), nicht eine Funktion in der WHERE-Klausel.

5. DATE_FORMAT und CAST als Indexkiller

Neben YEAR() und LOWER() gibt es weitere häufige Funktionsaufrufe, die den Index deaktivieren. DATE_FORMAT(date, '%Y-%m') für monatliche Gruppenfilter und CAST(id AS CHAR) = '123' für Typkonvertierungen sind typische Kandidaten.

-- NON-SARGable: DATE_FORMAT on indexed column
SELECT * FROM sales_order
WHERE DATE_FORMAT(created_at, '%Y-%m') = '2024-06';

-- SARGable rewrite:
SELECT * FROM sales_order
WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';

-- NON-SARGable: CAST on indexed integer column
SELECT * FROM catalog_product_entity
WHERE CAST(entity_id AS CHAR) = '1234';

-- SARGable rewrite: compare integer to integer
SELECT * FROM catalog_product_entity
WHERE entity_id = 1234;

-- NON-SARGable: arithmetic on column
SELECT * FROM sales_order WHERE grand_total * 1.19 > 100;

-- SARGable rewrite: move calculation to the right side
SELECT * FROM sales_order WHERE grand_total > 100 / 1.19;

Der letzte Fall zeigt ein wichtiges Prinzip: Rechenoperationen auf der Spaltenseite deaktivieren ebenfalls den Index. Alles, was auf der Spalten-Seite einer WHERE-Bedingung steht, muss der Index-Wert sein — Transformationen gehören auf die Konstanten-Seite.

6. Funktionale Indizes als Ausweg (MySQL 8)

Manchmal lässt sich eine Funktion nicht vermeiden — zum Beispiel wenn eine Applikation Emails in gemischter Groß-/Kleinschreibung speichert und die Collation nicht geändert werden kann. Seit MySQL 8.0 können funktionale Indizes auf Ausdrücke statt auf reine Spalten erstellt werden. MySQL wertet den Ausdruck beim INSERT/UPDATE aus und speichert das Ergebnis im Index.

-- Create a functional index on LOWER(email)
-- (MySQL 8.0+, the expression must be in parentheses)
CREATE INDEX idx_email_lower ON customer_entity ((LOWER(email)));

-- Now this query CAN use the functional index:
SELECT customer_id FROM customer_entity
WHERE LOWER(email) = 'test@example.com';
-- key: idx_email_lower, type: ref

-- Verify index definition:
SHOW INDEX FROM customer_entity;
-- Expression column shows: lower(`email`)

Wichtig: Die Query muss die Funktion exakt so schreiben, wie sie im Index definiert wurde. LOWER(email) im Index und lower(email) in der Query sind in MySQL identisch (case-insensitive), aber LOWER(TRIM(email)) wäre ein anderer Ausdruck und würde den Index nicht nutzen.

7. Virtual Generated Columns mit Index

Eine Alternative zu funktionalen Indizes sind Virtual Generated Columns. Dabei wird eine neue Spalte definiert, deren Wert sich automatisch aus einem Ausdruck ergibt — und auf diese generierte Spalte kann ein normaler Index gelegt werden. Der Vorteil: Das Konzept ist auch in älteren MySQL-Versionen verfügbar und macht den berechneten Wert explizit sichtbar.

-- Add a virtual generated column for the year of creation
ALTER TABLE sales_order
    ADD COLUMN created_year SMALLINT
        GENERATED ALWAYS AS (YEAR(created_at)) VIRTUAL;

-- Create a regular index on the generated column
CREATE INDEX idx_created_year ON sales_order (created_year);

-- Now this query uses the index:
SELECT COUNT(*) FROM sales_order WHERE created_year = 2024;
-- key: idx_created_year, type: ref

-- Virtual = not stored on disk, computed on read
-- Stored generated columns ARE persisted but cost disk space

Virtual Generated Columns belegen keinen zusätzlichen Speicherplatz auf Disk (anders als STORED-Spalten). Der Wert wird bei Bedarf berechnet. Der Index auf der virtuellen Spalte speichert den berechneten Wert und erlaubt effiziente Suche — ohne dass die Applikation ihre Queries ändern muss, wenn der Index über den generierten Spaltenname angesprochen wird.

8. LIKE: Prefix-Suche vs. Volltextsuche

Ein häufig übersehener SARGable-Grenzfall ist die LIKE-Suche. MySQL kann einen Index bei LIKE nutzen — aber nur, wenn das Muster mit einem Präfix beginnt, also keinen führenden Wildcard hat. LIKE 'test%' kann einen Index nutzen, LIKE '%test%' nicht.

-- SARGable: prefix LIKE uses index (no leading wildcard)
EXPLAIN SELECT * FROM customer_entity
WHERE email LIKE 'admin%';
-- type: range, key: UNQ_CUSTOMER_ENTITY_EMAIL

-- NON-SARGable: leading wildcard forces full scan
EXPLAIN SELECT * FROM customer_entity
WHERE email LIKE '%gmail.com';
-- type: ALL, key: NULL

-- NON-SARGable: contains pattern
EXPLAIN SELECT * FROM customer_entity
WHERE email LIKE '%test%';
-- type: ALL, key: NULL

-- For full-text search needs, use FULLTEXT INDEX instead:
-- CREATE FULLTEXT INDEX idx_ft_name ON catalog_product_entity (name);
-- SELECT * FROM catalog_product_entity WHERE MATCH(name) AGAINST('leather bag');

Wer in Magento nach Produktnamen oder Kundendaten suchen muss, sollte für %contains%-Muster auf OpenSearch oder MySQLs FULLTEXT-Indizes umsteigen. Ein B-Tree-Index kann strukturell kein führendes Wildcard-Muster bedienen, weil der Anfang des gesuchten Werts unbekannt ist — und nur der Anfang erlaubt einen effizienten Sprung im sortierten Index.

10. Zusammenfassung

SARGable Query ist eines der wichtigsten Konzepte für effiziente SQL-Abfragen. Eine einzige Funktion auf einer indizierten Spalte — YEAR(created_at), LOWER(email), DATE_FORMAT(...) — deaktiviert den Index und erzwingt einen Full Table Scan. Das ist kein MySQL-Bug, sondern eine logische Konsequenz der B-Tree-Index-Architektur: Ein Index speichert Originalwerte, keine transformierten Werte.

Die Lösung ist in den meisten Fällen einfach: Bereichsfilter statt Datumsfunktionen, direkte Vergleiche statt LOWER(), Berechnungen auf die Konstantenseite verschieben. Wo Funktionen unvermeidbar sind, bieten funktionale Indizes (MySQL 8) und Virtual Generated Columns einen sauberen Ausweg, ohne die Applikationslogik zu verändern.

11. FAQ: SARGable Query