Hello. There is such a request

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`

The problem is that its execution time is critically high. The tables have up to 150k records.

In requests I am not an ace and I will be very grateful to you for any help. Thank!

What is required from the request: extract the amount of orders (from orders), number of unique visitors (from visits), conversion (by formula), click price (by formula) - all this is calculated for each product (products table).

Table relations: orders (parent_product_id, product_id) table x products (id) table visits (parent_product_id, product_id) table x products (id) table

  • For meaningful advice, we still need at least DDL tables and EXPLAIN queries. Not to mention the fact that when grouping by one field, the output of all fields in the table is very strange. For the presence of DISTINCT in all COUNT-ah means that pay16_products.id is never UNIQUE ... - Akina
  • @Akina, products.id - it is the UNIQUE 100% primary key, but on the occasion of DISTINCT, it duplicates the rows several times, which is why it chose this way) - Solvik
  • Here is the result of EXPLAIN http://prntscr.com/ebh9iz - Solvik
  • 2
    @vp_arth, does the view only help not to fence the request every time, but does not speed it up? - Solvik
  • one
    You have a strange attitude towards indexes .. No indexes - no problem? - vp_arth

2 answers 2

Try to alter the query like this:

 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

That is, we collect in advance the amounts and quantities we need in the necessary section in such a way that there would be no more than one record in the attached subqueries per product record. Firstly, we immediately get rid of distinct, secondly, due to the multiplication of records, you had wrong amounts for the seller / partner.

But for performance, since only part of the goods are selected, if this part is small compared to all the goods present in the database, it would be worthwhile to immediately combine product and visit, group them up to the product, and to this, cling up the products collected before the product. But if a sufficient majority of goods are selected, it is better to leave it here, because retrieving almost all records from the visits table by index will be several times slower than grouping by full reading of the table (or index).

  • Wow, this is a safe) I’m going to try now, yes, there are only 15 products extracted on the page - Solvik
  • this is super!) thanks, works!)) - Solvik
  • no problem, thanx a lot!) - Solvik

Since the necessary data is weakly linked, I suggest not to multiply the sample at least for the views:

 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

It is worth experimenting if mysql will not cache the result of a subquery for a particular (p.id) there are three possible subqueries for orders without a join and grouping will be better.

Having all the product data, the number of views, and data on orders (amounts, etc.), we can calculate all the necessary indicators in the external request

 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