Оптимизировать запрос $wpdb к базе данных WordPress

$querystr = "SELECT 
                 COUNT(post_id) AS cnt, post_id
             FROM 
                  ".$wpdb->prefix."postmeta
             WHERE 
                (meta_key = '_candidate_email' AND meta_value = 'xxx@gmail.com')
                OR
                (meta_key = 'hr' AND meta_value = '1') 
             GROUP BY 
                post_id
             HAVING 
                cnt = 2";
                
$my_res = $wpdb->get_results($querystr);

Давайте сперва разберёмся что делает ваш запрос и как он это делает. Я понятия не имею, где и что хранит вордпрес, но попробую воссоздать картину по фрагментам.

Запрос ищет мета-записи кандидатов с определённым имейлом или эйчара. Подозреваю, что эти записи относятся к одному post_id. Именно поэтому вы группируете по post_id и отбираете из результата только те post_id, для которых есть обе эти записи. Это что-то воде типа поста. Верно? Вы хотите найти посты со свойствами _candidate_email и hr и при этом с определёнными значениями этих свойств.

Ваш вариант работает так: он шпарит по всем строкам postmeta и проверяет их на соответствие условию where. Подходящие строки он собирает, группируя по post_id. И в конце он пробегает по собранным и сгруппированным строкам и отбрасывает все, что не соответствуют условию having. Кажется, что основное время он тратит на обход всех строк. То есть, при отсутствии индексов, запрос с join'ом всё равно будет медленный. Чтобы найти нужные записи в 2гб данных придётся потратить значительное время.

Индексы позволяют сократить объём информации, которую надо прошерстить. Не знаю существует ли индекс на meta_key, но для вашего запроса он был бы полезен. Судя по запросу в meta_key может храниться строка от 1 до, как минимум, 16 символов. Строки занимают довольно много памяти и индексировать их целиком не стоит. Но можно проиндексировать первые несколько символов meta_key. Например alter table postmeta add index meta_key (meta_key(5)). Число 5 означает, что индексировать следует только первые 5 символов значения meta_key (например, '_cand', 'hr'). Это работает как алфавитный указатель, вместо чем листать всю таблицу, вы сразу переходите к разделу "начинается на _cand". Вместо 5 можно попробовать и другие значения. Необходимо найти баланс между стоимостью индекса (он занимает память, перестраивается при каждом обновлении таблицы) и скоростью поиска.

Обратите внимание, что запрос alter может выполняться долго. Во время работы он заблокирует таблицу postmeta.

Самое оптимальное решение, которое я нашел:

$sql_email = "SELECT TAB_1.post_id FROM 
        (SELECT post_id
        FROM wp_postmeta
        WHERE (meta_key = '_candidate_email' AND meta_value = '$email')) AS TAB_1, 
        (SELECT post_id
        FROM wp_postmeta
        WHERE (meta_key = 'hr' AND meta_value = $hr)) AS TAB_2
        WHERE TAB_1.post_id = TAB_2.post_id";
$result = $wpdb->get_results($sql_email);

Может я непонятно объяснил, извиняюсь. Да, тут есть специфика wp, я об этом не подумал

Взял для теста базу, в которой таблица wp_postmeta занимает 2 ГБайт (8 млн записей), а wp_posts занимает 1.5 ГБайт (720 тыс записей).

Запрос по такой базе

SELECT COUNT(*) FROM wp_posts;

выполняется 32 секунды.

А

SELECT COUNT(*) FROM wp_postmeta;

-- 22 секунды. И это на довольно быстрой машине.

В таблице wp_postmeta имеется 500 тыс записей с meta_key = 'info_author' Для теста я добавил две записи, с уникальными post_id и значениями meta_key = info_author и meta_value 'Mike' и 'Nick'. Теперь запрос

SELECT
     post_id
FROM
    wp_postmeta
WHERE
    (meta_key = 'info_author'  AND meta_value = 'Mike')
   OR
    (meta_key = 'info_author' AND meta_value = 'Nick');

выполняется 13 секунд, что логично, потому что для поиска этих значений MySQL должен просмотреть всю таблицу wp_postmeta (на meta_value нет индекса).

Запрос, с которого ТС начал,

SELECT
    COUNT(post_id) AS cnt, post_id
FROM
    wp_postmeta
WHERE
    (meta_key = 'info_author' AND meta_value = 'Mike')
OR
    (meta_key = 'info_author' AND meta_value = 'Nick')
GROUP BY
    post_id
HAVING
        cnt = 2;

выполняется те же 13 секунд.

Запрос, указанный ТС в ответе, ещё медленнее:

SELECT TAB_1.post_id FROM
                         (SELECT post_id
                          FROM wp_postmeta
                          WHERE (meta_key = 'info_author' AND meta_value = 'Mike')) AS TAB_1,
                         (SELECT post_id
                          FROM wp_postmeta
                          WHERE (meta_key = 'info_author' AND meta_value = 'Nick')) AS TAB_2
WHERE TAB_1.post_id = TAB_2.post_id;

-- 19 секунд.

Не помогает и join на саму себя. Запрос

SELECT A.meta_value AS Value1, B.meta_value AS Value2, A.post_id
FROM wp_postmeta A, wp_postmeta B
WHERE
      (A.meta_key = 'info_author' AND A.meta_value = 'Mike')
  AND
      (B.meta_key = 'info_author' AND B.meta_value = 'Nick')
  AND
      A.post_id = B.post_id;

выполняется 20 секунд.

TL&DR

Здесь не поможет ничего, пока нет индекса на meta_value. Если искомые записи в конце, MySQL приходится перелопачивать всю таблицу.

Ставить индекс на meta_value - неправильно, он может исчезнуть при обновлении ядра WordPress.

Единственно нормальное решение - кешировать такие долгие запросы в transient, например. Тогда первый запрос будет медленным, а остальные выполнятся быстро. С кешем, впрочем, всегда одна проблема - когда его инвалидировать, но тут уж надо смотреть логику работы сайта.