Здравствуйте!
Посоветуйте, пожалуйста, как можно как-то ускорить некоторый sql запрос
Структура таблицы такая:
CREATE TABLE `wildberries` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`brand` VARCHAR(50) NULL DEFAULT NULL,
`subject` VARCHAR(50) NULL DEFAULT NULL,
`article` VARCHAR(50) NULL DEFAULT NULL,
`size` VARCHAR(50) NULL DEFAULT NULL,
`color` VARCHAR(50) NULL DEFAULT NULL,
`income` VARCHAR(50) NULL DEFAULT NULL,
`income_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`ordered` VARCHAR(50) NULL DEFAULT NULL,
`ordered_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`ordered_max_by_day` VARCHAR(50) NULL DEFAULT NULL,
`return_before_payment` VARCHAR(50) NULL DEFAULT NULL,
`return_before_payment_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`sales_by_payment` VARCHAR(50) NULL DEFAULT NULL,
`sales_by_payment_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`return` VARCHAR(50) NULL DEFAULT NULL,
`return_sum_price` VARCHAR(50) NULL DEFAULT NULL,
`stock` VARCHAR(50) NULL DEFAULT NULL,
`source_file` VARCHAR(50) NULL DEFAULT NULL,
`from` DATE NULL DEFAULT NULL,
`end` DATE NULL DEFAULT NULL,
`clear_article` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `from` (`from`),
INDEX `end` (`end`),
INDEX `size_color_clear_article` (`size`, `color`, `clear_article`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=28744;
Текст запроса такой: (сгенерирован автоматически)
SELECT `w`.`brand` AS `brand`, `w`.`article` AS `article`, `w`.`subject` AS `subject`, `w`.`size` AS `size`, `w`.`color` AS `color`, `w`.`clear_article` AS `clear_article`, (
SELECT SUM(IFNULL(income,0)) AS `income`) AS `income`, (
SELECT SUM(IFNULL(income_sum_price,0)) AS `income_sum_price`) AS `income_sum_price`, (
SELECT SUM(IFNULL(ordered,0)) AS `ordered`) AS `ordered`, (
SELECT SUM(IFNULL(ordered_sum_price,0)) AS `ordered_sum_price`) AS `ordered_sum_price`, (
SELECT MAX(ordered_max_by_day) AS `ordered_max_by_day`) AS `ordered_max_by_day`, (
SELECT SUM(IFNULL(return_before_payment,0)) AS `return_before_payment`) AS `return_before_payment`, (
SELECT SUM(IFNULL(return_before_payment_sum_price,0)) AS `return_before_payment_sum_price`) AS `return_before_payment_sum_price`, (
SELECT SUM(IFNULL(sales_by_payment,0)) AS `sales_by_payment`) AS `sales_by_payment`, (
SELECT SUM(IFNULL(sales_by_payment_sum_price,0)) AS `sales_by_payment_sum_price`) AS `sales_by_payment_sum_price`, (
SELECT SUM(IFNULL(`return`,0)) AS ```return```) AS `return`, (
SELECT SUM(IFNULL(`return_sum_price`,0)) AS ```return_sum_price```) AS `return_sum_price`, (
SELECT (
SELECT `stock`
FROM wildberries w2
WHERE w2.clear_article = w.clear_article AND w2.size = w.size AND w2.color = w.color
ORDER BY `end` DESC
LIMIT 1) AS ```stock```) AS `stock`, (
SELECT SUM(IF (sales_by_payment IS NOT NULL, sales_by_payment_sum_price / sales_by_payment, NULL)) AS `sale_price`) AS `sale_price`, (
SELECT SUM(income_sum_price * 0.62) AS `our_percent`) AS `our_percent`, (
SELECT SUM(IFNULL(income_sum_price,0) * 0.25) AS `cost_price_income`) AS `cost_price_income`, (
SELECT SUM(IFNULL(sales_by_payment_sum_price,0) - (IFNULL(income_sum_price,0) * 0.25)) AS `profit`) AS `profit`, (
SELECT IF(income IS NOT NULL, ((sales_by_payment - `return`) / income) * 100, 0) AS `sales_from_income`) AS `sales_from_income`, (
SELECT (SUM(sales_by_payment) / SUM(return_before_payment)) * 100 AS `returns_buyout`) AS `returns_buyout`, (
SELECT SUM(return_before_payment) / SUM(ordered) * 100 AS `returns`) AS `returns`, (
SELECT SUM(sales_by_payment) / SUM(ordered) * 100 AS `buyout`) AS `buyout`, (
SELECT SUM(IFNULL(income,0) - IFNULL(sales_by_payment,0) - IFNULL(`return`,0)) AS `total`) AS `total`, (
SELECT (SUM(IFNULL(income,0)) - `stock` - SUM(IFNULL(`sales_by_payment`,0)) + SUM(IFNULL(`return`,0))) AS `in_the_way`) AS `in_the_way`, (
SELECT IF(sales_by_payment IS NOT NULL, sales_by_payment_sum_price / sales_by_payment, 0) AS `average_sum_price`) AS `average_sale_sum_price`
FROM `wildberries` AS `w`
WHERE `from` >= '2016-03-01' AND `end` <= '2017-06-30'
GROUP BY `clear_article`, `size`, `color`
ORDER BY `article` DESC
Собственно, меня смущает подзапрос внутри, но как его заменить пока идей нет.
Средний
