Tutorial: Finding a Slow meta_query and Rewriting It as a Taxonomy Filter

A filter written as a meta_query looks harmless in PHP. Three conditions, an array each, a relation between them. What MySQL receives is a different thing entirely: three more joins on a table that holds every custom field of every post in the installation, and whose relevant column carries no index at all.

The rewrite below has four steps – read the SQL, measure it, move the filter into a taxonomy, migrate the values once. It is worth doing for exactly one kind of field, and the last section says which.

What WP_Query Builds Out of the Array

A query with three conditions on custom fields is short to write and long to execute.

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

Every clause becomes its own join with its own alias, because a single row of wp_postmeta can only carry one key at a time. Three conditions therefore need three copies of the table.

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

The decisive detail is in the last line of each condition. wp_postmeta ships with an index on post_id and one on meta_key, and none on meta_value – the column is a LONGTEXT and cannot be indexed in full. MySQL can therefore find all rows with the key farbe quickly, and then has to look at each of them to see whether the value is blau.

How expensive that is depends on a number nobody watches: how many rows share the key. A field set on every product has as many rows as there are products, and three such fields multiply during the join.

Finding the Query Instead of Guessing at It

Before anything is rewritten, the query has to be on the table. Two lines in wp-config.php make WordPress keep every statement it sends.

define( 'SAVEQUERIES', true );

After that, the slowest statements of a page request can be listed – together with the call path that produced them, which is usually the more interesting half.

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

On a live site the same information comes out of the slow query log without touching any code, and that is the better source because it covers requests nobody is watching.

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

Reading the EXPLAIN Output

A statement in hand, one command says what MySQL intends to do with it.

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

Three columns carry the finding. rows estimates how many rows are examined per table, and the product across all tables is roughly the work involved. key names the index used – meta_key here, never meta_value. And Extra holds the two remarks worth reacting to: Using temporary and Using filesort.

The second of those has a cause of its own. Sorting by a custom field turns the sort into a text comparison over an unindexed column, which is why an added 'orderby' => 'meta_value_num' often costs more than all three filters together.

Moving the Filter into a Taxonomy

The reason a taxonomy is faster has nothing to do with WordPress being clever about it. It is that wp_term_relationships stores two integers per row and has a primary key over exactly those two columns, so the join runs over an index from both directions.

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

Two settings deserve a moment. public => false keeps the archive pages and the URLs out of the site, which is what a pure filter attribute wants. And show_admin_column costs nothing and makes the values visible in the post list, which is where a wrong migration shows up first.

The query afterwards describes the same filter with a different vocabulary.

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

A note on the third condition from the original query: a yes-or-no field such as verfuegbar does not belong in a taxonomy either. Two terms over ten thousand products means one term carries nine thousand relationships, and an index over a column with two distinct values is close to useless. Such a flag is better placed in post_status, in a separate status taxonomy alongside other states, or left in postmeta as the single remaining clause – one join is not a problem, three are.

Migrating the Existing Values Once

The taxonomy exists, the posts know nothing about it yet. A one-off run over WP-CLI transfers the values and can be checked before it writes anything.

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";
'

Three details decide whether this run is repeatable. post_status => any also catches drafts and scheduled posts, which are exactly the ones that turn up missing later. The false in wp_set_object_terms replaces the assignment instead of adding to it, so a second run produces the same state rather than a growing list. And sanitize_title means the term is found by its slug, so Blau and blau end up in the same term instead of two.

The postmeta rows are best kept for a while. Once the new path has been running for a few weeks, they can go in one statement – and only then, because that step is the one without a way back.

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

When a Taxonomy Is the Wrong Answer

The rewrite pays off for fields with few distinct values that are used as filters. It is the wrong move for three other cases, and each has its own better answer.

Numeric ranges are the clearest of them. A price, a date, a weight – anything queried with BETWEEN or a comparison has no terms to speak of, and a taxonomy with eight thousand terms is worse than the postmeta it replaced. Such a field belongs in its own column, in a custom table with an index over exactly the combination being queried.

Full-text search is the second. Terms match exactly or not at all; a search for part of a word needs a FULLTEXT index or a separate search index, and neither is a taxonomy.

And values that exist on only a handful of posts are the third. A field on twenty of forty thousand products is already selective through meta_key alone, and the query is fast without any change. The tool for that decision is a single count, taken before any rewriting starts.

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;

Many rows and few distinct values is the case a taxonomy was made for. Many rows and almost as many distinct values calls for a column. Few rows means nothing needs to happen at all.