Как упростить 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 |
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?
Дополнительно:
Покажите EXPLAIN запроса
также отладить/создать индексы имеет смысл после того как запрос отладите
Ответы:
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 |
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
- да, не дурно... индекс на op.item_id и o. date тоже имеет смысл повесить все равно
Что-то у меня дежавю. Недавно вы с похожим вопросом сюда приходили.
Не задания ли это для какого-нибудь теста?
Да и вам намекали, что отвечать на такие вопросы удобнее, если вы не вот эту хрень рисуете в описании таблиц, а sql-дамп метаданных приводите. Там видно и ключи, и ссылки, и индексы. Да и выполнить такой SQL для эксперимента можно мгновенно. А вы игнорируете рекомендации.
Такого рода сортировки по сложным сложных джойнам и на довольно больших объёмах быстро работать не будут. Такие вещи решают денормализацией.
Можно сделать отдельную аггрегированную таблицу, которая будет 1к1 джойниться к вашей тблице продуктов и предоствлять уже посчитанные данные.
Такую таблицу считают первично один раз и на это не жалко 350с, а потом её можно поддерживать в акутальном состоянии триггерами или допилив бэкенд. Всё таки запись в аггрегируемые таблицы по времени не так критична обычно, как выборка этой статистики.
- коммон, какая тут денормализация
обычные 2 таблицы и одна связующая... типовой кейсобъемы мелкие
- Нет, делаю работы по одному из интернет-магазинов и есть желание сделать статистику по продажам каждого товара именно из отдельной таблицы, т.е. не создавая к каждому товару отдельное поле где будет подсчет количества продаж и т.д. Похожий вопрос был, буквально недавно и на основе его решения я сделал вышеуказанную структуру запроса. Но не понимаю, разве этот проект не для вопросов?
- siroper, для вопросов, но не для заданий. Ок, если не задание, то норм.
есть желание сделать статистику по продажам каждого товара именно из отдельной таблицы, т.е. не создавая к каждому товару отдельное поле где будет подсчет количества продаж
Это что значит? Вы не хотите хранить аггрегированное значение и инкрементировать его триггером?
- Максим Федоров, если по агрегатам хочется сортировать, то не такие уж и мелкие, ведь сортировать и агрегировать придётся все продукты, даже те. которые не будут выведенены и будут обрезаны лимитом.
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 |
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
- Group by order by не для всех архитектур субд возможно
- Максим Федоров, В тэгах MySQL
- Rsa97, вот он и не умеет
- Максим Федоров, С чего вы это взяли? Умеет
https://www.db-fiddle.com/f/e3L1xsHjWWvYBWKU6yG9YV/0
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для упрощения SQL запроса существует несколько способов. Вот несколько рекомендаций, которые могут помочь вам упростить ваш запрос:
1. Используйте агрегатные функции: Если вам нужно получить сумму, среднее значение или количество строк, используйте агрегатные функции вместо использования циклов или подзапросов.
SELECT SUM(column_name) FROM table_name;
2. Используйте операторы JOIN: Вместо использования подзапросов для объединения таблиц, используйте операторы JOIN, такие как INNER JOIN, LEFT JOIN, RIGHT JOIN или FULL JOIN.
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
3. Оптимизируйте условия WHERE: Используйте операторы сравнения (=, , >, =, <=) и логические операторы (AND, OR, NOT) для оптимизации условий WHERE.
SELECT column_name(s) FROM table_name WHERE condition1 AND condition2;
4. Используйте индексы: Создание индексов на столбцах, по которым вы часто выполняете поиск или сортировку, поможет ускорить выполнение запроса.
CREATE INDEX index_name ON table_name (column_name);
5. Используйте подзапросы с умом: Используйте подзапросы только тогда, когда они действительно необходимы, и старайтесь делать их как можно более простыми.
SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT column_name FROM another_table);
6. Проверьте план выполнения запроса: Используйте инструменты анализа выполнения запросов, чтобы оптимизировать запросы и улучшить производительность базы данных.
Надеюсь, эти советы помогут вам упростить ваши SQL запросы и повысить их эффективность. Если у вас возникнут дополнительные вопросы, не стесняйтесь задавать их!