Tutorial: Eine langsame meta_query finden und in einen Taxonomie-Filter umbauen

Ein Filter, der als meta_query geschrieben ist, sieht in PHP harmlos aus. Drei Bedingungen, je ein Array, dazwischen eine Verknüpfung. Was bei MySQL ankommt, ist etwas ganz anderes: drei zusätzliche Verbindungen auf eine Tabelle, die jedes Zusatzfeld jedes Beitrags der Installation enthält und deren entscheidende Spalte überhaupt keinen Index trägt.

Der folgende Umbau hat vier Schritte – das SQL lesen, es messen, den Filter in eine Taxonomie verlegen, die Werte einmalig übertragen. Er lohnt sich für genau eine Art von Feld, und der letzte Abschnitt sagt, für welche.

Was WP_Query aus dem Array baut

Eine Abfrage mit drei Bedingungen auf Zusatzfeldern ist kurz geschrieben und lang ausgeführt.

$q = new WP_Query( [
    'post_type'  => 'produkt',
    'meta_query' => [
        'relation' => 'AND',
        [ 'key' => 'farbe',     'value' => 'blau' ],
        [ 'key' => 'material',  'value' => 'eiche' ],
        [ 'key' => 'verfuegbar','value' => '1' ],
    ],
] );

Jede Klausel wird zu einer eigenen Verbindung mit eigenem Alias, denn eine einzelne Zeile in wp_postmeta trägt immer nur einen Schlüssel. Drei Bedingungen brauchen deshalb drei Kopien der Tabelle.

SELECT wp_posts.ID FROM wp_posts
  INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
  INNER JOIN wp_postmeta AS mt1 ON wp_posts.ID = mt1.post_id
  INNER JOIN wp_postmeta AS mt2 ON wp_posts.ID = mt2.post_id
WHERE wp_posts.post_type = 'produkt'
  AND ( wp_postmeta.meta_key = 'farbe'      AND wp_postmeta.meta_value = 'blau' )
  AND ( mt1.meta_key        = 'material'    AND mt1.meta_value        = 'eiche' )
  AND ( mt2.meta_key        = 'verfuegbar'  AND mt2.meta_value        = '1' )
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

Das Entscheidende steht in der jeweils letzten Zeile jeder Bedingung. wp_postmeta bringt einen Index auf post_id mit und einen auf meta_key, aber keinen auf meta_value – die Spalte ist ein LONGTEXT und lässt sich nicht vollständig indizieren. MySQL findet also alle Zeilen mit dem Schlüssel farbe schnell und muss danach jede einzelne davon ansehen, um den Wert blau zu prüfen.

Wie teuer das wird, hängt an einer Zahl, die niemand beobachtet: wie viele Zeilen sich den Schlüssel teilen. Ein Feld, das an jedem Produkt hängt, hat so viele Zeilen wie es Produkte gibt – und drei solche Felder vervielfachen sich in der Verbindung.

Die Abfrage finden statt sie zu erraten

Bevor etwas umgebaut wird, gehört die Abfrage auf den Tisch. Zwei Zeilen in der wp-config.php lassen WordPress jede abgesetzte Anweisung mitschreiben.

define( 'SAVEQUERIES', true );

Danach lassen sich die langsamsten Anweisungen eines Seitenaufrufs auflisten – samt dem Aufrufpfad, der sie erzeugt hat, und der ist meist die interessantere Hälfte.

add_action( 'shutdown', function () {
    global $wpdb;
    if ( ! defined( 'SAVEQUERIES' ) || ! SAVEQUERIES ) { return; }
    $langsam = array_filter( $wpdb->queries, fn( $q ) => $q[1] > 0.05 );
    usort( $langsam, fn( $a, $b ) => $b[1] <=> $a[1] );
    foreach ( array_slice( $langsam, 0, 5 ) as $q ) {
        error_log( sprintf( "%.3f s  %s\n   %s", $q[1], $q[2], substr( $q[0], 0, 400 ) ) );
    }
} );

Auf einer laufenden Seite liefert dieselbe Information das Protokoll langsamer Abfragen, ganz ohne Eingriff im Code – und das ist die bessere Quelle, weil es auch Aufrufe erfasst, die niemand beobachtet.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Die EXPLAIN-Ausgabe lesen

Liegt die Anweisung vor, sagt ein Befehl, was MySQL damit vorhat.

EXPLAIN SELECT wp_posts.ID FROM wp_posts
  INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
  ...

Drei Spalten tragen den Befund. rows schätzt, wie viele Zeilen je Tabelle betrachtet werden, und das Produkt über alle Tabellen ist ungefähr der Aufwand. key nennt den benutzten Index – hier meta_key, niemals meta_value. Und in Extra stehen die beiden Anmerkungen, auf die sich reagieren lohnt: Using temporary und Using filesort.

Die zweite davon hat eine eigene Ursache. Eine Sortierung nach einem Zusatzfeld macht aus dem Sortieren einen Textvergleich über eine nicht indizierte Spalte – deshalb kostet ein ergänztes 'orderby' => 'meta_value_num' oft mehr als alle drei Filter zusammen.

Den Filter in eine Taxonomie verlegen

Dass eine Taxonomie schneller ist, liegt nicht daran, dass WordPress dabei besonders klug vorginge. Es liegt daran, dass wp_term_relationships je Zeile zwei ganze Zahlen speichert und einen Primärschlüssel über genau diese beiden Spalten trägt – die Verbindung läuft also aus beiden Richtungen über einen Index.

add_action( 'init', function () {
    register_taxonomy( 'farbe', 'produkt', [
        'hierarchical'      => false,
        'public'            => false,
        'show_ui'           => true,
        'show_admin_column' => true,
        'query_var'         => false,
        'rewrite'           => false,
    ] );
} );

Zwei Einstellungen verdienen einen Moment. public => false hält Archivseiten und Adressen aus dem Auftritt heraus, und genau das will ein reines Filtermerkmal. Und show_admin_column kostet nichts und macht die Werte in der Beitragsliste sichtbar – dort fällt eine misslungene Übertragung zuerst auf.

Die Abfrage danach beschreibt denselben Filter mit anderem Vokabular.

$q = new WP_Query( [
    'post_type' => 'produkt',
    'tax_query' => [
        'relation' => 'AND',
        [ 'taxonomy' => 'farbe',    'field' => 'slug', 'terms' => 'blau' ],
        [ 'taxonomy' => 'material', 'field' => 'slug', 'terms' => 'eiche' ],
    ],
] );

Ein Wort zur dritten Bedingung der ursprünglichen Abfrage: Ein Ja-Nein-Feld wie verfuegbar gehört ebenfalls nicht in eine Taxonomie. Zwei Begriffe auf zehntausend Produkte bedeuten, dass ein Begriff neuntausend Zuordnungen trägt, und ein Index über eine Spalte mit zwei verschiedenen Werten ist nahezu wertlos. Ein solches Kennzeichen ist in post_status besser aufgehoben, in einer eigenen Statustaxonomie neben anderen Zuständen – oder es bleibt als einzige verbleibende Klausel im Postmeta: Eine Verbindung ist kein Problem, drei sind es.

Die vorhandenen Werte einmalig übertragen

Die Taxonomie existiert, die Beiträge wissen noch nichts davon. Ein einmaliger Lauf über WP-CLI überträgt die Werte und lässt sich vorher prüfen.

wp eval '
$ids = get_posts( [ "post_type" => "produkt", "posts_per_page" => -1,
                    "fields" => "ids", "post_status" => "any" ] );
$n = 0;
foreach ( $ids as $id ) {
    $wert = get_post_meta( $id, "farbe", true );
    if ( $wert === "" ) { continue; }
    wp_set_object_terms( $id, sanitize_title( $wert ), "farbe", false );
    $n++;
}
echo "$n Beitraege\n";
'

Drei Einzelheiten entscheiden, ob dieser Lauf wiederholbar ist. post_status => any erfasst auch Entwürfe und geplante Beiträge – genau die, die später als fehlend auffallen. Das false in wp_set_object_terms ersetzt die Zuordnung, statt sie zu ergänzen, sodass ein zweiter Lauf denselben Zustand erzeugt und keine wachsende Liste. Und sanitize_title sorgt dafür, dass der Begriff über seinen Slug gefunden wird, sodass aus Blau und blau ein Begriff wird statt zwei.

Die Postmeta-Zeilen bleiben am besten eine Weile stehen. Läuft der neue Weg einige Wochen, verschwinden sie mit einer Anweisung – und erst dann, denn dieser Schritt ist der ohne Rückweg.

wp post meta delete --all --keys=farbe --dry-run

Wann eine Taxonomie die falsche Antwort ist

Der Umbau rechnet sich für Felder mit wenigen verschiedenen Werten, die als Filter dienen. Für drei andere Fälle ist er der falsche Griff, und jeder hat seine eigene bessere Antwort.

Am deutlichsten sind Zahlenbereiche. Ein Preis, ein Datum, ein Gewicht – alles, was mit BETWEEN oder einem Vergleich abgefragt wird, hat keine nennenswerten Begriffe, und eine Taxonomie mit achttausend Begriffen ist schlechter als das ersetzte Postmeta. Ein solches Feld gehört in eine eigene Spalte, in eine eigene Tabelle mit einem Index über genau die abgefragte Kombination.

Die Volltextsuche ist der zweite Fall. Begriffe treffen genau oder gar nicht; eine Suche nach einem Wortteil braucht einen FULLTEXT-Index oder einen eigenen Suchindex, und beides ist keine Taxonomie.

Der dritte Fall sind Werte, die nur an einer Handvoll Beiträge hängen. Ein Feld an zwanzig von vierzigtausend Produkten ist schon über meta_key allein trennscharf, und die Abfrage ist ohne jede Änderung schnell. Das Werkzeug für diese Entscheidung ist eine einzige Zählung, vor jedem Umbau erhoben.

SELECT meta_key, COUNT(*) AS zeilen, COUNT(DISTINCT meta_value) AS werte
FROM wp_postmeta GROUP BY meta_key ORDER BY zeilen DESC LIMIT 20;

Viele Zeilen und wenige verschiedene Werte – das ist der Fall, für den eine Taxonomie gemacht ist. Viele Zeilen und fast ebenso viele verschiedene Werte verlangen nach einer Spalte. Wenige Zeilen bedeuten, dass gar nichts geschehen muss.