wp_postmeta lento: índice em meta_key e meta_value para meta_query

A listagem do seu custom post type com meta_query rodava em 200 ms e hoje leva oito segundos. No slow query log é sempre a mesma coisa: JOIN em wp_postmeta varrendo dezenas de milhares de linhas. O motivo está no esquema do WordPress: o índice padrão é KEY meta_key (meta_key(191)), e meta_value fica de fora.

Primeiro olhe o EXPLAIN da query real

Não adivinhe o SQL: pegue o que o WP_Query montou. Salve num arquivo com a tag de abertura do PHP — sem ela o eval-file só imprime o texto:

<?php
$q = new WP_Query([
    'post_type'      => 'imovel',
    'posts_per_page' => 12,
    'meta_query'     => [['key' => 'cidade', 'value' => 'Curitiba']],
]);
echo $q->request;

# sudo -u www-data wp eval-file /tmp/q.php

Copie a saída, ponha EXPLAIN na frente e rode com sudo -u www-data wp db query. Na linha de wp_postmeta o key vem como meta_key, com número gordo em rows e “Using where”: o MySQL pega toda linha cuja meta_key é cidade e só depois compara o valor, uma a uma.

O índice composto

ALTER TABLE wp_postmeta
  ADD INDEX meta_key_value (meta_key(191), meta_value(20)),
  ALGORITHM=INPLACE, LOCK=NONE;

Os 191 caracteres são o mesmo prefixo que o núcleo já usa em meta_key, e vinte de meta_value bastam para separar os candidatos antes da comparação final. O ALGORITHM=INPLACE com LOCK=NONE mantém a tabela gravável durante a criação, mas só no InnoDB: em MyISAM o MySQL copia a tabela e trava escrita o tempo todo. Confira a engine e tenha um dump antes.

O preço que ninguém conta

Índice não é de graça. wp_postmeta é a tabela mais escrita num WordPress com campos customizados, e agora todo INSERT mantém mais um índice: importação de catálogo e cron que grava meta em massa ficam mais lentos. Meça:

SELECT ROUND(data_length/1048576) AS dados_mb, ROUND(index_length/1048576) AS indice_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE() AND table_name = 'wp_postmeta';

E não espere milagre em ordenação: prefixo de índice não ordena nada. Se a meta é numérica e o orderby usa meta_value_num, o ganho é pequeno; aí o caminho é tirar o campo quente do postmeta para uma coluna própria.