Как я могу улучшить медленный пользовательский запрос? (подзапрос + внутренние соединения с настраиваемыми полями)

У меня есть запрос, который используется для возврата доступных номеров для системы бронирования

Используя Расширенные настраиваемые поля, у меня есть настраиваемый тип сообщения, представляющий резервирование с различными полями.

План здесь состоит в том, чтобы найти все забронированные комнаты с подзапросом, а затем получить все доступные комнаты, выбрав сообщения, которых нет в результирующем наборе, возвращаемом подзапросом.

В моих пользовательских постах бронирования есть "terrain_id", который должен соответствовать идентификатору поста комнат (кемпинг или коттеджи).

Первое внутреннее соединение предназначено для идентификатора комнаты (terrain_id), второе внутреннее соединение - для даты начала, а последнее - для даты окончания.

Итак, в основном я сопоставляю room_id с post_id и возвращаю все недоступные комнаты за указанный интервал времени, а затем я получаю все IDS, которые не присутствуют в результате подзапроса, чтобы получить мои доступные комнаты

Проблема в следующем:

Этот запрос выполняется примерно за 5-6 секунд на крошечной выборке данных (около 2000 бронирований и 100 номеров).

Я пробовал разные способы оптимизировать его, но в значительной степени застрял

По всем соответствующим полям есть указатели

Сам подзапрос выполняется примерно за 0,030 секунды, поэтому я не думаю, что проблемы здесь возникают с внутренними соединениями, основная часть проблемы, похоже, находится в SELECT, где НЕ СУЩЕСТВУЕТ

Использование методов wordpress по умолчанию и цикла не является вариантом, поскольку невозможно сделать то, что необходимо, и мне нужно будет запросить все настраиваемые поля индивидуально для каждой записи.

Как я могу повысить производительность запросов?

Как можно так долго выбирать базовые атрибуты, которых нет в массиве из примерно 100 записей?

SELECT wps.ID, wps.post_title, wps.post_type, wps.post_status
FROM wp_posts wps, wp_postmeta wpm
WHERE NOT EXISTS
(
    SELECT
      p.ID,
      pm_ti.meta_value as tiv,
      pm_ti.meta_key as tik,
      pm_ed.meta_key as edk,
      pm_ed.meta_value as edv,
      pm_sd.meta_key as sdk,
      pm_sd.meta_value as sdv
    FROM
      wp_posts p
    INNER JOIN wp_postmeta pm_ti ON (pm_ti.post_id = p.ID )
    INNER JOIN wp_postmeta pm_ed ON (pm_ed.post_id = p.ID )
    INNER JOIN wp_postmeta pm_sd ON (pm_sd.post_id = p.ID )
    WHERE NOT (pm_ed.meta_value <= '{$start}' OR pm_sd.meta_value >= '{$end}')
    AND pm_ti.meta_key='terrain_id' and p.post_status='publish' and wps.ID = pm_ti.meta_value and pm_ed.meta_key='e_date' and pm_sd.meta_key='s_date'
    GROUP BY p.ID
)
AND wpm.post_id = wps.ID AND wps.post_status='publish' AND wps.ID = icl_translations.element_id
and (wps.post_type='camping' or wps.post_type='cottages') GROUP BY wps.ID
");

Объясните результат:


mysql wordpress php sql advanced-custom-fields
person user1229829    schedule 08.07.2014    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Я недостаточно хорошо знаю ваши данные, чтобы написать это на 100%, но идея здесь та же самая. Вместо использования коррелированного подзапроса попробуйте что-нибудь вроде этого.

SELECT wps.ID, wps.post_title, wps.post_type, wps.post_status
FROM wp_posts wps, wp_postmeta wpm
WHERE wpm.post_id NOT IN
(
    SELECT
      DISTINCT pm_ti.meta_value
    FROM
      wp_posts p
    INNER JOIN wp_postmeta pm_ti ON (pm_ti.post_id = p.ID )
    INNER JOIN wp_postmeta pm_ed ON (pm_ed.post_id = p.ID )
    INNER JOIN wp_postmeta pm_sd ON (pm_sd.post_id = p.ID )
    WHERE NOT (pm_ed.meta_value <= '{$start}' OR pm_sd.meta_value >= '{$end}')
    AND pm_ti.meta_key='terrain_id'
    AND p.post_status='publish'
    AND pm_ed.meta_key='e_date'
    AND pm_sd.meta_key='s_date'
)
AND wpm.post_id = wps.ID
AND wps.post_status='publish'
AND wps.ID = icl_translations.element_id
AND (wps.post_type='camping' or wps.post_type='cottages')

Идея состоит в том, чтобы вернуть список идентификаторов, которые вам не нужны, в одном выборе, который не связан с основным выбором. Затем вы фильтруете свой основной выбор, используя NOT IN.

Просто попробовать - не проверял.

person Bob    schedule 08.07.2014
comment
Запрос выполняется намного быстрее, но проблема в том, что вместо p.ID первичным ключом должен быть meta_key 'terrain_id'. Я пытаюсь заставить его работать, и я отчитаюсь - person user1229829; 08.07.2014
comment
Я обновил это, чтобы вернуть значение terrain_id и сравнить его с post_id, но на самом деле это не имеет никакого смысла, поскольку вы все равно устанавливаете id равным post_id. Меня очень смущает ваша база данных. Может быть, я уже устал думать сейчас. - person Bob; 08.07.2014
comment
@Bob - это стандартная структура базы данных WordPress. Он основан на структуре тегов TOXI Solution и поэтому сбивает с толку. - person random_user_name; 08.07.2014
comment
Подзапрос используется для поиска доступных комнат, POST ID - это номер бронирования, а terrain_id - это номер комнаты, поэтому мне нужен номер комнаты вместо номера бронирования, чтобы найти доступную комнату по номеру. Но да, я согласен, структура довольно запутанная. Я попробовал несколько настроек, но запрос по-прежнему выполняется примерно через 5 секунд. Как и в случае с моим первым запросом, проблема по-прежнему находится в SELECT WHERE wps.post_ID NOT IN, что занимает около 90% времени запроса. - person user1229829; 08.07.2014

arrow_upward
0
arrow_downward

Мне наконец удалось найти достойный ответ

Источником проблемы был тот факт, что вложенные запросы, подобные этим, по своей сути медленные, поэтому я заменил WHERE ... NOT IN отрицательным LEFT OUTER JOIN.

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

Затем, заменив условие WHERE на LEFT OUTER JOIN, чтобы получить тот же результат

Этот вопрос был очень полезным:

Вложенный SQL-запрос медленно при использовании IN

Исходный запрос занял от 5 до 15 секунд,

Измененный запрос выполняется менее чем за 0,05 секунды.

SELECT wps.ID, wps.post_title, pm_ti.meta_value

FROM wp_posts wps

LEFT OUTER JOIN 
(

   SELECT
   pm_ti.meta_value
   FROM
   wp_postmeta pm_ti
   INNER JOIN wp_postmeta pm_ed ON (pm_ed.post_id = pm_ti.post_id )
   INNER JOIN wp_postmeta pm_sd ON (pm_sd.post_id = pm_ti.post_id )
   WHERE NOT (pm_ed.meta_value <= '2014-07-01' OR pm_sd.meta_value >= '2014-07-12')
   AND pm_ti.meta_key='terrain_id' AND pm_ed.meta_key='e_date' AND pm_sd.meta_key='s_date'

) pm_ti

ON wps.ID = pm_ti.meta_value

INNER JOIN wp_icl_translations icl ON (icl.element_id = wps.ID)

WHERE pm_ti.meta_value IS null AND wps.post_status='publish' 

AND (wps.post_type='camping' OR wps.post_type='cottages')  AND icl.language_code = 'fr'

GROUP BY wps.ID
person user1229829    schedule 08.07.2014