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

A slow product filter deserves measurement before a data-model change. A meta_query can be appropriate; a taxonomy can help categorical filters. Neither the number of joins nor the number of distinct values alone predicts the faster solution. The first requirement is that the rewritten query still selects the same products.

1. Preserve the complete filter

The example searches published products with colour blau, material eiche and availability 1. The original and rewritten query must keep all three conditions, the same ordering and the same pagination. Availability remains a metadata condition here.

<?php
$base = [
    'post_type' => 'produkt',
    'post_status' => 'publish',
    'posts_per_page' => 20,
    'paged' => 1,
    'orderby' => ['date' => 'DESC', 'ID' => 'DESC'],
];
$before = new WP_Query($base + [
    'meta_query' => [
        'relation' => 'AND',
        ['key' => 'farbe', 'value' => 'blau'],
        ['key' => 'material', 'value' => 'eiche'],
        ['key' => 'verfuegbar', 'value' => '1'],
    ],
]);
$after = new WP_Query($base + [
    'tax_query' => [
        'relation' => 'AND',
        ['taxonomy' => 'farbe', 'field' => 'slug',
         'terms' => ['blau'], 'include_children' => false],
        ['taxonomy' => 'material', 'field' => 'slug',
         'terms' => ['eiche'], 'include_children' => false],
    ],
    'meta_query' => [
        ['key' => 'verfuegbar', 'value' => '1'],
    ],
]);
// SQL: $before->request and $after->request

This code assumes the custom post type produkt and both taxonomies already exist and contain the correct assignments. It is not a WooCommerce query. It executes two queries for comparison; the application would use the chosen version. Equality must be checked across all relevant pages and filter combinations, not only the first twenty results.

2. Inspect actual SQL and its plan

$query->request contains the generated SQL. The three positive metadata conditions joined by AND normally require three metadata joins in this example. This is not a universal one-clause/one-join rule: compatible OR clauses can reuse an alias. Logical table aliases are not physical copies of the database table.

The standard WordPress schema indexes post_id and a prefix of meta_key, but not meta_value. Existing installations may have extra indexes. The optimizer can start from the posts table or another useful access path; it does not invariably scan every row with a particular key. Duplicate metadata rows can expand intermediate results.

For a protected development request, SAVEQUERIES records SQL, execution time and calling information. It adds memory and processing overhead and can expose sensitive values in logs. A database slow-query log provides a different perspective and does not supply the same PHP call path. Its activation requires appropriate privileges, an explicit logging period and consideration of volume. Changing a global session-default setting does not update every existing connection.

EXPLAIN describes an estimated plan. rows and filtered must be read together with join order, access type and selected indexes. A large estimate alone does not prove that a filter is ineffective. Using filesort means an additional sorting operation, not necessarily a disk sort. Using temporary is not by itself proof of a bad query. MySQL’s EXPLAIN ANALYZE actually executes the statement; MariaDB syntax and behavior must be checked separately.

meta_value_num is numeric ordering, implemented by WordPress as a numeric expression, not a text comparison. Expressions and large intermediate results can increase work, but the earlier claim that sorting normally costs more than all filters had no measured basis.

3. Register both categorical attributes

<?php
add_action('init', function () {
    foreach (['farbe', 'material'] as $taxonomy) {
        register_taxonomy($taxonomy, ['produkt'], [
            'hierarchical' => false,
            'public' => false,
            'publicly_queryable' => false,
            'show_ui' => true,
            'show_admin_column' => true,
            'show_in_rest' => false,
            'query_var' => false,
            'rewrite' => false,
        ]);
    }
});

Both farbe and material are registered. The example disables public taxonomy queries and rewrite routes, while allowing administration. This is not an authorization boundary. Block-editor access requires an appropriate REST configuration, including show_in_rest; REST exposure and permissions need their own decision.

term_relationships contains object ID, term-taxonomy ID and term order. Its composite primary key starts with the object ID, and a separate index starts with the term-taxonomy ID. These access paths can help exact category matching. A composite index is not automatically equally useful from either end. A boolean flag is not inherently unsuitable for a taxonomy, and publication status should not be repurposed merely to encode stock availability.

4. Plan migration before writing

The following plan.php is deliberately read-only. It covers two example IDs and explicit value mappings; both lists need adapting to the actual inventory. Target terms must already exist. Unknown, empty, duplicate or structured metadata stops the plan instead of silently losing information. Empty values need an explicit policy: retain an assignment, remove it or flag it for review.

<?php
// Read-only example for reviewed IDs, not a complete migration runner.
$ids = [101, 102];
$mapping = [
    'farbe' => ['blau' => 'blau', 'rot' => 'rot'],
    'material' => ['eiche' => 'eiche', 'buche' => 'buche'],
];
$plan = [];
foreach ($ids as $id) {
    if (get_post_type($id) !== 'produkt') {
        throw new RuntimeException("Unexpected post type: $id");
    }
    foreach ($mapping as $taxonomy => $values) {
        if (!taxonomy_exists($taxonomy)) {
            throw new RuntimeException("Missing taxonomy: $taxonomy");
        }
        $raw = get_post_meta($id, $taxonomy, false);
        if (count($raw) !== 1 || !is_string($raw[0])
            || !array_key_exists($raw[0], $values)) {
            throw new RuntimeException("Unmapped or ambiguous value: $id/$taxonomy");
        }
        $term = get_term_by('slug', $values[$raw[0]], $taxonomy);
        if (!$term || is_wp_error($term)) {
            throw new RuntimeException("Missing target term: $id/$taxonomy");
        }
        $old = wp_get_object_terms($id, $taxonomy, ['fields' => 'ids']);
        if (is_wp_error($old)) {
            throw new RuntimeException($old->get_error_message());
        }
        $plan[] = [
            'post_id' => $id,
            'taxonomy' => $taxonomy,
            'source_value' => $raw[0],
            'old_term_ids' => array_map('intval', $old),
            'new_term_ids' => [(int) $term->term_id],
        ];
    }
}
echo wp_json_encode($plan, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);

Execution with wp eval-file plan.php prints the proposed assignments. It performs no term assignment or metadata deletion. A complete migration additionally needs bounded batches, a backup of source values and old assignments, a write freeze or synchronization of every writer, and a checked resume strategy.

After the plan has been reviewed and the current source values and old assignments have been compared again, the following API fragment applies one row. Integer term IDs prevent confusion with numeric term names. The false argument replaces assignments within this taxonomy, so existing terms may be removed.

<?php
// Fragment for one previously reviewed and revalidated plan row.
$result = wp_set_object_terms(
    (int) $row['post_id'],
    array_map('intval', $row['new_term_ids']),
    $row['taxonomy'],
    false
);
if (is_wp_error($result)) {
    throw new RuntimeException($result->get_error_message());
}
// Read back term IDs and compare them with new_term_ids.

The fragment is not an atomic migration runner. Each write needs read-back verification and error handling; a later failure does not automatically roll back earlier rows. Re-running an old plan after taxonomy edits can overwrite newer work. Automatic slug normalization can merge distinct source values, which is why the mapping is explicit.

5. Validate behavior before retiring metadata

Comparisons should include result IDs, ordering, counts, missing values, duplicate metadata, drafts and scheduled content in migration scope, and multilingual assignments where relevant. Query latency should be measured with comparable data, indexes, cache conditions and workload. No reproducible measurement supports the earlier graphic’s 480,000-versus-2,100-row claim.

The old command wp post meta delete --all --keys=farbe --dry-run was invalid for this task. The documented command requires an object ID; --all means all metadata for that object, and no such dry-run option is documented. Old metadata should remain until writers, comparisons and rollback are resolved. Cleanup needs its own explicit scope and backup.

Sources

• WP_Meta_Query: compatible aliases
• register_taxonomy
• wp_set_object_terms
• WP-CLI: post meta delete
• MySQL: EXPLAIN output
• MySQL: slow query log
• WordPress: debugging
• WP_Query