Search the Community

Добрый день. Есть вот такой код. Общий смысл - формируется матрица из товаров, остатков и продаж по ним за определенные интервалы времени. Его выполнение на 700 товарах вешает сервер минут на 15-20. Может быть его можно оптимизировать? Буду благодарен за советы. public function getProducts($sort, $order, $language_id, $quantity_order_total_date_from, $quantity_order_total_date_to, $quantity_order_year_date_from, $quantity_order_year_date_to) { $query = $this->db->query("SELECT p.product_id, p.location, p.quantity, pd.name, pdata.n_price, pdata.n_wt, pdata.coef, m.name AS manufacturer, (SELECT label FROM " . DB_PREFIX . "labels AS l WHERE l.product_id = p.product_id LIMIT 1) AS label, (SELECT SUM(ibp.income_ord) FROM " . DB_PREFIX . "income_box_product AS ibp JOIN " . DB_PREFIX . "income_box AS ib ON (ib.income_box_id = ibp.income_box_id) WHERE ibp.product_id = p.product_id AND ib.status <> '1') AS quantity_income, (SELECT SUM(op1.quantity) FROM " . DB_PREFIX . "order_product AS op1 JOIN " . DB_PREFIX . "order AS o1 ON (o1.order_id = op1.order_id) WHERE op1.product_id = p.product_id AND o1.order_status_id <> 7 AND UNIX_TIMESTAMP(o1.date_added) >= " . (int) $quantity_order_year_date_from . " AND UNIX_TIMESTAMP(o1.date_added) <= " . (int) $quantity_order_year_date_to . " GROUP BY op1.product_id) AS quantity_order_1, (SELECT SUM(op2.quantity) FROM " . DB_PREFIX . "order_product AS op2 JOIN " . DB_PREFIX . "order AS o2 ON (o2.order_id = op2.order_id) WHERE op2.product_id = p.product_id AND o2.order_status_id <> 7 AND UNIX_TIMESTAMP(o2.date_added) >= " . (int) ($quantity_order_year_date_from - ($quantity_order_total_date_to - $quantity_order_total_date_from)) . " AND UNIX_TIMESTAMP(o2.date_added) <= " . (int) ($quantity_order_year_date_from - 86400) . " GROUP BY op2.product_id) AS quantity_order_2, (SELECT SUM(op3.quantity) FROM " . DB_PREFIX . "order_product AS op3 JOIN " . DB_PREFIX . "order AS o3 ON (o3.order_id = op3.order_id) WHERE op3.product_id = p.product_id AND o3.order_status_id <> 7 AND UNIX_TIMESTAMP(o3.date_added) >= " . (int) ($quantity_order_total_date_from - ($quantity_order_total_date_to - $quantity_order_total_date_from)) . " AND UNIX_TIMESTAMP(o3.date_added) <= " . (int) ($quantity_order_total_date_from - 86400). " GROUP BY op3.product_id) AS quantity_order_3 FROM `" . DB_PREFIX . "product` AS p JOIN `" . DB_PREFIX . "product_description` AS pd ON (p.product_id = pd.product_id) JOIN `" . DB_PREFIX . "manufacturer` AS m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN `" . DB_PREFIX . "product_data` AS pdata ON (pdata.product_id = p.product_id) WHERE pd.language_id = '" . (int) $language_id . "' AND p.status = 1 ORDER BY " . ($sort == 'm.name' ? $sort . " " . $order . ", pd.name ASC" : $sort . " " . $order . ", m.name ASC")); return $query->rows; }