Как упростить запрос с группировкой, максимальным значением и JOIN в MySQL?
Попробуйте следующее:SELECT
  pd.product_id,
  d.name,
  d.discount_percent,
  d.discount_amount
FROM s_products_discount pd
JOIN s_discounts d ON pd.discount_id = d.id
WHERE 1
AND pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994') # проверять список ид ДО более сложной проверки
AND d.priority = (
  SELECT MAX(sd.priority)
  FROM s_products_discount spd
  JOIN s_discounts sd on spd.discount_id = sd.id
  WHERE spd.product_id = pd.product_id
  AND sd.visible = 1
  AND sd.date_from <= '2018-05-05'
  AND sd.date_to >= '2018-05-05'
)
У меня только возник вопрос... А зачем везде LEFT JOIN? Разве в текущей задаче нужен именно он, а не просто join? Такое чувство, что left пишется просто по привычке.
В чем состояла оптимизация - вы строите временную таблицу, где к каждому продукту ищется максимальный доступный приоритет. Если представить, что у нас пол миллиона продуктов, вы хотим выбрать только десяток - все равно будет построена таблица по всем продуктам. Я же переместила подзапрос в where, где будет сравниваться приоритет для конкретного продукта. Судя по моим маленьким тестам, даже на малом количестве данных запрос не проигрывает, на большом количестве данных разница должна быть заметнее.
В подзапросе таблица s_products_discount  не нужна, можно вот так попробовать:SELECT pd.product_id,
       d.name,
       d.discount_percent,
       d.discount_amount
  FROM s_products_discount pd
  LEFT JOIN s_discounts     d
         ON d.id = pd.discount_id 
  LEFT JOIN (SELECT MAX(d.priority) as max_priority
               FROM s_discounts dd
              WHERE dd.id         = pd.discount_id
                AND dd.visible    = 1
                AND dd.date_from <= '2018-05-05'
                AND dd.date_to   >= '2018-05-05') pp 
         ON pp.max_priority = d.priority
 WHERE pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994')