Очень медленно выполняется MySQL запрос

302

24 февраля 2017, 00:53

Всем привет. Имеется вот такой вот запрос

SELECT
  pay16_products.*,
  (SUM(pay16_orders.seller_summ) + SUM(pay16_orders.partner_summ)) / COUNT(DISTINCT pay16_visits.id) AS orders_summ,
  COUNT(DISTINCT pay16_orders.id)                                                                    AS count_orders,
  COUNT(DISTINCT pay16_visits.id)                                                                    AS count_visits,
  COUNT(DISTINCT pay16_orders.id) / COUNT(DISTINCT pay16_visits.id) * 100                            AS conversion,
  (SUM(pay16_orders.seller_summ) + SUM(pay16_orders.partner_summ)) / COUNT(DISTINCT pay16_visits.id) /
  COUNT(DISTINCT pay16_visits.id)                                                                    AS cpc
FROM `pay16_products`
  LEFT JOIN `pay16_orders` ON `pay16_orders`.`product_parent_id` = `pay16_products`.`id` AND `pay16_orders`.`status` = 1
  LEFT JOIN `pay16_product_visits` AS `pay16_visits` ON `pay16_visits`.`product_parent_id` = `pay16_products`.`id`
WHERE `pay16_products`.`active` = 1 AND `pay16_products`.`active_partners_system` = 1 AND
      `pay16_products`.`parent_id` IS NULL
GROUP BY `pay16_products`.`id`

Проблема в том, что время его выполнения критично высоко. В таблицах имеется до 150к записей.

В запросах я не ас и буду очень признателен вам за любую помощь. Спасибо!

Что требуется от запроса: извлечь сумму заказов (из orders), кол-во уникальных посетителей (из visits), конверсию (формулой), цену клика (формулой) - все это посчитать для каждого товара (таблица products).

Связи таблиц: Таблица orders (parent_product_id, product_id) x Таблица products (id) Таблица visits (parent_product_id, product_id) x Таблица products (id)

Answer 1

Попробуйте переделать запрос примерно так:

SELECT prod.*,
       ord.summ / visit.cnt AS orders_summ,
       ord.cnt AS count_orders,
       visit.cnt AS count_visits,
       ord.cnt / visit.cnt * 100 AS conversion,
       ord.summ / visit.cnt / visit.cnt AS cpc
  FROM `pay16_products` prod
  LEFT JOIN (
    select product_parent_id, SUM(seller_summ) + SUM(partner_summ) as summ, count(1) as cnt
      from `pay16_orders`
     where `status` = 1
     group by `product_parent_id`
  ) ord on ord.product_parent_id=prod.id
  LEFT JOIN (
    select product_parent_id, count(1) as cnt
      from pay16_product_visits
     group by product_parent_id
  ) visit on visit.product_parent_id=prod.id
  WHERE prod.`active` = 1 AND prod.`active_partners_system` = 1
    AND prod.`parent_id` IS NULL

То есть, заранее собираем нужные нам суммы и количества в нужном разрезе таким образом, что бы на одну запись продуктов приходилось не более одной записи в присоединяемых подзапросах. Во первых мы сразу избавляемся от distinct, во вторых из за перемножения записей у вас были неверные суммы по seller/partner.

Но для производительности, так как выбирается только часть товаров, если эта часть мала по сравнению со всеми присутствующими товарами в БД стоило бы сразу объединить product и visit, сгруппировать до продукта и к этому уже цеплять собранные до продукта order. Но если отбирается достаточная большая часть товаров, то лучше оставить как тут, потому что получение по индексу почти всех записей таблицы visits будет в разы медленнее, чем группировка полным чтением таблицы (или индекса).

Answer 2

Так как необходимые данные слабо связаны, предлагаю не множить выборку хотя бы на просмотры:

SELECT
  p.name product_name, -- и др. нужные поля
  (SELECT COUNT(pv.id) FROM pay16_visits pv WHERE pv.product_parent_id = p.id) visits,
  COUNT(po.id) orders,
  SUM(po.seller_summ) sell_sum,
  SUM(po.partner_summ) part_sum,
  FROM pay16_products p
  LEFT JOIN pay16_orders po ON po.product_parent_id = p.id AND po.status = 1
  WHERE p.active = 1 AND p.parent_id is null
    AND p.active_partners_system = 1)
  GROUP BY p.id

Стоит поэкспериментировать, если mysql не будет кешировать результат подзапроса для конкретного(p.id) возможно три подзапроса для заказов без джойна и группировки будут лучше.

Имея все данные продукта, число просмотров, и данные по заказам(суммы и тп.), мы можем посчитать все необходимые показатели во внешнем запросе

SELECT ag.product_name, ag.visits, ag.orders, ag.sell_sum, ag.part_sum,
  (ag.sell_summ+ag.part_summ) / ag.visits orders_summ,
  (ag.sell_summ+ag.part_summ) / ag.visits / ag.visits cpc, -- квадрат посещений?
FROM (SELECT ...) ag