Как оптимизировать или создать правильно mysql запрос?

Question

[Kakto-Tak]

Есть две таблицы:
orders [ id | order_id | shipment_date ] - таблица с заказами
orders_items [ id | sku | order_id | quantity ] - таблица с товарами заказа
Задача: получить последнюю запись по конкретному SKU, а точнее его "order_id" за 20 дней до текущего момента, но при этом количество товаров в данном заказе должно быть равно "1" и в единственном экземпляре.
Получился такой запрос:

SELECT orders.order_id,shipment_date FROM orders left join orders_items ON (orders_items.order_id = orders.order_id) WHERE orders_items.sku='$sku' and place_order=0 and (select count(id) from orders_items where order_id=orders.order_id)=1 and orders_items.quantity=1 and shipment_date<=(NOW() - INTERVAL 20 day) order by orders.id desc limit 1

Но, выполняется он чуть ли не пару минут. Уже сломал голову, как оптимизировать. Попробовал даже новомодные нейросети, но те выдают вот такой нерабочий запрос:

SELECT o.order_id, o.shipment_date FROM orders o WHERE o.place_order = 0 AND o.shipment_date <= (NOW() - INTERVAL 20 DAY) AND EXISTS ( SELECT 1 FROM orders_items oi WHERE oi.order_id = o.order_id AND oi.sku = '$sku' AND oi.quantity = 1 ) GROUP BY o.order_id HAVING COUNT(o.order_id) = 1 ORDER BY o.id DESC LIMIT 1;

Что скажете, в каком направлении копать?

Answer 1

[Михаил Р.]

(select count(id) from orders_items where order_id=orders.order_id)=1

Запрос будет выполняться для каждой строки, которую найдет бд (10000 заказов - 10000 выполнится поиск) - это не очень эффективно...

Можно попробовать найти товар в orders_items, затем присоединить таблицу orders, затем использовать not exist, чтобы убедиться, что в заказе нет других записей:

SELECT
    o.order_id,
    o.shipment_date
FROM
    orders_items oi

        -- Присоединяем таблицу заказов, чтобы получить доступ к дате и статусу.
        INNER JOIN
    orders o ON oi.order_id = o.order_id
WHERE

    -- Находим строки конкретного товара.
    oi.sku = '$sku'
  AND oi.quantity = 1

  -- Фильтры таблицы заказов.
  AND o.place_order = 0
  
  -- Отсекаем лишнее по дате.
  AND o.shipment_date <= (NOW() - INTERVAL 20 DAY)

  -- В этом заказе не должно быть других позиций.
  AND NOT EXISTS (
    SELECT 1
    FROM orders_items oi_check
    WHERE oi_check.order_id = oi.order_id

      -- Ищем любую запись с тем же order_id, но другим id.
      AND oi_check.id <> oi.id
)
ORDER BY
    o.id DESC
    LIMIT 1;

P.S. этот запрос будет производительный лишь в том случае, если у вас есть индексы. Проверьте наличие индексов у orders_items(sku) (лучше составной (sku, quantity)), orders_items(order_id) и orders(order_id).

Answer 2

[Андрей Ежгуров]

При использовании группировки можно обойтись без вложенных запросов.

SELECT o.order_id, MAX(o.shipment_date) AS shipment_date
FROM orders AS o
INNER JOIN orders_items AS oi ON oi.order_id = o.order_id
WHERE o.shipment_date <= NOW() - INTERVAL 20 DAY
GROUP BY o.order_id
HAVING SUM(oi.quantity) = 1 AND MAX(oi.sku) = '$sku'
ORDER BY o.shipment_date DESC
LIMIT 1

MAX используется для соответствия стандарту SQL. В MySQL оба MAX вероятно можно опустить.

P.S. И, да: наличие нужных индексов многократно ускоряет запросы.