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

Question

[siroper]

Здравствуйте. Имеется 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?

Comments

[ScriptKiddo]

Покажите EXPLAIN запроса

[siroper]

ScriptKiddo,

[Максим Федоров]

у вас подзапросы идут на одну таблицу — имеет смысл приджойнить эту таблицу, сделать группировку и в полях сделать switch case

также отладить/создать индексы имеет смысл после того как запрос отладите

[ScriptKiddo]

siroper, индексы на таблицах orders_products, orders

Answer 1

[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

Comments

[Максим Федоров]

да, не дурно... индекс на op.item_id и o. date тоже имеет смысл повесить все равно

Answer 2

[Сергей П]

Что-то у меня дежавю. Недавно вы с похожим вопросом сюда приходили.
Не задания ли это для какого-нибудь теста?
Да и вам намекали, что отвечать на такие вопросы удобнее, если вы не вот эту хрень рисуете в описании таблиц, а sql-дамп метаданных приводите. Там видно и ключи, и ссылки, и индексы. Да и выполнить такой SQL для эксперимента можно мгновенно. А вы игнорируете рекомендации.

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

Такую таблицу считают первично один раз и на это не жалко 350с, а потом её можно поддерживать в акутальном состоянии триггерами или допилив бэкенд. Всё таки запись в аггрегируемые таблицы по времени не так критична обычно, как выборка этой статистики.

Comments

[Максим Федоров]

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

объемы мелкие

[siroper]

Нет, делаю работы по одному из интернет-магазинов и есть желание сделать статистику по продажам каждого товара именно из отдельной таблицы, т.е. не создавая к каждому товару отдельное поле где будет подсчет количества продаж и т.д. Похожий вопрос был, буквально недавно и на основе его решения я сделал вышеуказанную структуру запроса. Но не понимаю, разве этот проект не для вопросов?

[Сергей П]

siroper, для вопросов, но не для заданий. Ок, если не задание, то норм.

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

Это что значит? Вы не хотите хранить аггрегированное значение и инкрементировать его триггером?

[Сергей П]

Максим Федоров, если по агрегатам хочется сортировать, то не такие уж и мелкие, ведь сортировать и агрегировать придётся все продукты, даже те. которые не будут выведенены и будут обрезаны лимитом.

Answer 3

[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

Comments

[Максим Федоров]

Group by order by не для всех архитектур субд возможно

[Rsa97]

Максим Федоров, В тэгах MySQL

[Максим Федоров]

Rsa97, вот он и не умеет

[Rsa97]

Максим Федоров, С чего вы это взяли? Умеет
https://www.db-fiddle.com/f/e3L1xsHjWWvYBWKU6yG9YV/0