Tutorial: Jak znaleźć wolne meta_query i przepisać je na filtr taksonomii

Filtr napisany jako meta_query wygląda w PHP niewinnie. Trzy warunki, każdy jako tablica, a między nimi powiązanie. To, co dociera do MySQL, jest czymś zupełnie innym: trzy dodatkowe złączenia na tabeli zawierającej każde pole dodatkowe każdego wpisu w instalacji, której rozstrzygająca kolumna nie ma żadnego indeksu.

Poniższa przebudowa ma cztery kroki – odczytać SQL, zmierzyć go, przenieść filtr do taksonomii, jednorazowo przenieść wartości. Opłaca się dokładnie dla jednego rodzaju pola, a ostatni rozdział mówi, dla którego.

Co WP_Query buduje z tablicy

Zapytanie z trzema warunkami na polach dodatkowych pisze się krótko, a wykonuje długo.

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

Każda klauzula staje się osobnym złączeniem z własnym aliasem, bo pojedynczy wiersz wp_postmeta niesie zawsze tylko jeden klucz. Trzy warunki potrzebują więc trzech kopii tabeli.

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

Rozstrzygające stoi w ostatniej linii każdego warunku. wp_postmeta przynosi indeks na post_id oraz na meta_key, ale żadnego na meta_value – kolumna jest typu LONGTEXT i nie da się jej zaindeksować w całości. MySQL znajduje więc szybko wszystkie wiersze z kluczem farbe, a potem musi obejrzeć każdy z nich, aby sprawdzić wartość blau.

Jak drogie to będzie, zależy od liczby, której nikt nie obserwuje: ile wierszy dzieli ten sam klucz. Pole obecne przy każdym produkcie ma tyle wierszy, ile jest produktów – a trzy takie pola mnożą się w złączeniu.

Znalezienie zapytania zamiast zgadywania

Zanim cokolwiek zostanie przebudowane, zapytanie musi leżeć na stole. Dwie linie w wp-config.php każą WordPressowi zapisywać każdą wysłaną instrukcję.

define( 'SAVEQUERIES', true );

Potem da się wypisać najwolniejsze instrukcje jednego wywołania strony – wraz ze ścieżką wywołań, która je stworzyła, a to zwykle ciekawsza połowa.

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 ) ) );
    }
} );

Na działającej witrynie tę samą informację daje dziennik wolnych zapytań, całkiem bez ingerencji w kod – i to lepsze źródło, bo obejmuje także wywołania, których nikt nie obserwuje.

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

Odczyt wyniku EXPLAIN

Gdy instrukcja jest już znana, jedno polecenie mówi, co MySQL zamierza z nią zrobić.

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

Wynik niosą trzy kolumny. rows szacuje, ile wierszy zostaje obejrzanych w każdej tabeli, a iloczyn po wszystkich tabelach to z grubsza nakład pracy. key podaje użyty indeks – tutaj meta_key, nigdy meta_value. A w Extra stoją dwie uwagi warte reakcji: Using temporary i Using filesort.

Druga z nich ma własną przyczynę. Sortowanie po polu dodatkowym zamienia sortowanie w porównanie tekstu na kolumnie bez indeksu – dlatego dodane 'orderby' => 'meta_value_num' kosztuje często więcej niż wszystkie trzy filtry razem.

Przeniesienie filtra do taksonomii

To, że taksonomia jest szybsza, nie bierze się z jakiejś szczególnej przemyślności WordPressa. Bierze się stąd, że wp_term_relationships przechowuje w wierszu dwie liczby całkowite i ma klucz główny właśnie po tych dwóch kolumnach – złączenie biegnie więc z obu stron przez indeks.

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

Dwa ustawienia zasługują na chwilę uwagi. public => false trzyma strony archiwum i adresy poza witryną, a właśnie tego chce czysta cecha filtrująca. A show_admin_column nic nie kosztuje i pokazuje wartości na liście wpisów – tam nieudane przeniesienie rzuca się w oczy najwcześniej.

Zapytanie po zmianie opisuje ten sam filtr innym słownictwem.

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

Słowo o trzecim warunku pierwotnego zapytania: pole typu tak-nie, jak verfuegbar, również nie należy do taksonomii. Dwa terminy na dziesięć tysięcy produktów oznaczają, że jeden termin niesie dziewięć tysięcy przypisań, a indeks na kolumnie o dwóch różnych wartościach jest niemal bezwartościowy. Taki znacznik lepiej mieści się w post_status, w osobnej taksonomii stanów obok innych stanów – albo zostaje w postmeta jako jedyna pozostała klauzula: jedno złączenie nie jest problemem, trzy są.

Jednorazowe przeniesienie istniejących wartości

Taksonomia istnieje, wpisy nic o niej jeszcze nie wiedzą. Jednorazowy przebieg przez WP-CLI przenosi wartości i daje się wcześniej sprawdzić.

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 wpisow\n";
'

O powtarzalności tego przebiegu rozstrzygają trzy szczegóły. post_status => any obejmuje także szkice i wpisy zaplanowane – właśnie te, których brak ujawnia się później. false w wp_set_object_terms zastępuje przypisanie, zamiast je uzupełniać, dzięki czemu drugi przebieg daje ten sam stan, a nie rosnącą listę. A sanitize_title sprawia, że termin zostaje znaleziony po slugu, więc z Blau i blau powstaje jeden termin, a nie dwa.

Wiersze postmeta najlepiej zostawić jeszcze na jakiś czas. Gdy nowa droga podziała kilka tygodni, znikną jedną instrukcją – i dopiero wtedy, bo ten krok nie ma drogi powrotnej.

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

Kiedy taksonomia jest złą odpowiedzią

Przebudowa opłaca się przy polach o niewielu różnych wartościach, służących jako filtr. Dla trzech innych przypadków jest chwytem błędnym, a każdy z nich ma własną lepszą odpowiedź.

Najwyraźniejsze są zakresy liczbowe. Cena, data, waga – wszystko, o co pyta się przez BETWEEN albo porównanie, nie ma godnych uwagi terminów, a taksonomia z ośmioma tysiącami terminów jest gorsza od zastąpionego postmeta. Takie pole należy do własnej kolumny, we własnej tabeli z indeksem po dokładnie tej odpytywanej kombinacji.

Drugim przypadkiem jest wyszukiwanie pełnotekstowe. Terminy trafiają dokładnie albo wcale; szukanie części słowa wymaga indeksu FULLTEXT albo osobnego indeksu wyszukiwania, a żadne z nich nie jest taksonomią.

Trzecim przypadkiem są wartości wiszące tylko przy garstce wpisów. Pole przy dwudziestu z czterdziestu tysięcy produktów jest wystarczająco rozróżniające już przez sam meta_key, a zapytanie działa szybko bez żadnej zmiany. Narzędziem tej decyzji jest jedno zliczenie, wykonane przed każdą przebudową.

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;

Wiele wierszy i mało różnych wartości – to przypadek, dla którego taksonomia powstała. Wiele wierszy i prawie tyle samo różnych wartości domaga się kolumny. Mało wierszy oznacza, że nie musi się dziać nic.