@siroper

Как упростить SQL запрос?

Здравствуйте. Имеется 2 таблицы в БД, одна с товарами, другая с историей заказов каждого товара.

orders:
[id]--[date]--[status]

products:
[id]--[name]--[price]--[active]

orders_products:
[id]--[product_id]--[order_id]--[count]--[price]

order_id - id заказа
count - количество данного продукта в заказе
price - цена единицы

products примерно 10 000
orders_products примерно 50 000

Запрос работает, занимает 700 миллисек:

SELECT p.*, 
	(
        SELECT COUNT(op.id) 
        FROM orders_products op 
        LEFT Join orders o
        ON op.order_id = o.id
        WHERE 
        	op.item_id = p.id AND 
        	o.date > "2020-01-01" AND
        	o.status != 2
    ) AS ordersCount,
	(
        SELECT SUM(op.count) 
        FROM orders_products op 
        LEFT Join orders o
        ON op.order_id = o.id
        WHERE 
        	op.item_id = p.id AND 
        	o.date > "2020-01-01" AND
        	o.status != 2
    ) AS countOrdersCount,
	(
        SELECT SUM(op.price * op.count) 
        FROM orders_products op 
        LEFT Join orders o
        ON op.order_id = o.id
        WHERE 
        	op.item_id = p.id AND 
        	o.date > "2020-01-01" AND
        	o.status != 2
    ) AS ordersSum
FROM products p 
WHERE p.active='1' 
ORDER BY p.id DESC
LIMIT 0,25


Но если я делаю сортировку по количеству заказов / продаж / суммы продаж, например вместо ORDER BY p.id DESC - ставлю ORDER BY ordersCount DESC, то запрос занимает порядка 350 секунд!

Как в такой конструкции можно сделать более отзывчивую в плане скорости запроса сортировку по ordersCount / countOrdersCount / ordersSum?
  • Вопрос задан
  • 163 просмотра
Пригласить эксперта
Ответы на вопрос 3
Adamos
@Adamos
SELECT * 
FROM products p 
WHERE p.active='1' 
ORDER BY p.id DESC
LIMIT 0,25

SELECT op.order_id, 
COUNT(op.id) AS ordersCount, 
SUM(op.count) AS countOrdersCount, 
SUM(op.price * op.count) AS ordersSum
FROM orders_products op 
LEFT Join orders o
ON op.order_id = o.id
WHERE 
op.order_id IN (/* всего лишь 25 значений из предыдущего запроса */) AND
op.item_id = p.id AND 
o.date > "2020-01-01" AND
o.status != 2
Ответ написан
trapwalker
@trapwalker
Программист, энтузиаст
Что-то у меня дежавю. Недавно вы с похожим вопросом сюда приходили.
Не задания ли это для какого-нибудь теста?
Да и вам намекали, что отвечать на такие вопросы удобнее, если вы не вот эту хрень рисуете в описании таблиц, а sql-дамп метаданных приводите. Там видно и ключи, и ссылки, и индексы. Да и выполнить такой SQL для эксперимента можно мгновенно. А вы игнорируете рекомендации.

Такого рода сортировки по сложным сложных джойнам и на довольно больших объёмах быстро работать не будут. Такие вещи решают денормализацией.
Можно сделать отдельную аггрегированную таблицу, которая будет 1к1 джойниться к вашей тблице продуктов и предоствлять уже посчитанные данные.

Такую таблицу считают первично один раз и на это не жалко 350с, а потом её можно поддерживать в акутальном состоянии триггерами или допилив бэкенд. Всё таки запись в аггрегируемые таблицы по времени не так критична обычно, как выборка этой статистики.
Ответ написан
Rsa97
@Rsa97
Для правильного вопроса надо знать половину ответа
SELECT `p`.*,
       COUNT(`op`.`id`) AS `ordersCount`,
       IFNULL(SUM(`op`.`count`), 0) AS `countOrdersCount`
       IFNULL(SUM(`op`.`price` * `op`.`count`), 0) AS `ordersSum`
  FROM `products` AS `p`
  LEFT JOIN `orders_products` AS `op` ON `op`.`item_id` = `p`.`id`
  LEFT JOIN `orders` AS `o` ON `o`.`id` = `op`.`order_id`
    AND `o`.`date` > '2020-01-01'
    AND `o`.`status` != 2
  WHERE `p`.`active` = '1' 
  GROUP BY `p`.`id`
  ORDER BY `p`.`id` DESC
  LIMIT 0, 25
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы