title: mysql, slow query, mb partition or not?
body: <p>there is such a structure of tables in the database (mysql 8.0.26), not the whole database.
  now it works like this. you need to display products linked to any entity - seller, category, brand, search phrase. first, a query is executed that sorts the goods by some column - revenue, rating, name, number of sales. then 100 products are selected, information for the final table is calculated based on them. if the next 100 products are needed, then the offset in the sorting query will be executed. A request that receives information on goods counts data for 30 days, plans to increase the period to 90 days. If there are more than 90 thousand products, the request does not have time to be executed or rests on the fact that memory has run out. will portioning help in this case? or how do they cope with such a load?</p>
  <ol>
  <li>product</li>
  </ol>
  <blockquote>
  <p>id - int, create_at, status, group_id, pics, is_new, lost_sale</p>
  </blockquote>
  <ol start="2">
  <li>product_group</li>
  </ol>
  <blockquote>
  <p>id, brand_id, seller_id, name</p>
  </blockquote>
  <ol start="3">
  <li>product_log</li>
  </ol>
  <blockquote>
  <p>create_at, product_id, price, price_discount, rating, comment, order_count, sales_count, quantity</p>
  </blockquote>
  <ol start="4">
  <li>category_product</li>
  </ol>
  <blockquote>
  <p>id, category_id, product_id</p>
  </blockquote>
  <p>as an example of sorting - sorts goods by revenue and gets 100 pieces</p>
  <pre><code>WITH temp_products AS (SELECT `p`.`id`        AS `id`,
                                `p`.`create_at` AS `create_at`,
                                `p`.`lost_sale` AS `revenue_lost`,
                                `pg`.`name`     AS `name`,
                                `b`.`id`        AS `brand_id`,
                                `b`.`name`      AS `brand_name`,
                                `s`.`id`        AS `seller_id`,
                                `s`.`name`      AS `salesman_name`,
                                `s`.`ogrn`      AS `salesman_ogrn`,
                                `s`.`trademark` AS `salesman_trademark`
                         FROM `product` `p`
                              LEFT JOIN `product_group` `pg` ON p.group_id = pg.id
                              LEFT JOIN `seller` `s` ON pg.seller_id = s.id
                              LEFT JOIN `brand` `b` ON pg.brand_id = b.id
                         WHERE `pg`.`seller_id` = '414336'),
   base AS (SELECT `p`.`id`, `pl`.`create_at`, (pl.price_discount * pl.order_count) AS `revenue`
            FROM `temp_products` `p`
                     join `product_log` `pl` ON p.id = pl.product_id
            WHERE `pl`.`create_at` BETWEEN 1659294000 AND 1661886000),
   summary AS (SELECT `id`, sum(revenue) AS `revenue` FROM `base` GROUP BY `id`)
  SELECT `id`, `revenue`
  FROM `summary`
  ORDER BY `revenue` DESC
  LIMIT 100
  </code></pre>
  <p>collects information on 100 products</p>
  <pre><code>WITH products AS (SELECT `p`.`id`        AS `id`,
                           `p`.`create_at` AS `create_at`,
                           `p`.`lost_sale` AS `revenue_lost`,
                           `pg`.`name`     AS `name`,
                           `b`.`id`        AS `brand_id`,
                           `b`.`name`      AS `brand_name`,
                           `s`.`id`        AS `seller_id`,
                           `s`.`name`      AS `salesman_name`,
                           `s`.`ogrn`      AS `salesman_ogrn`,
                           `s`.`trademark` AS `salesman_trademark`
                    FROM `product` `p`
                         LEFT JOIN `product_group` `pg` ON p.group_id = pg.id
                         LEFT JOIN `seller` `s` ON pg.seller_id = s.id
                         LEFT JOIN `brand` `b` ON pg.brand_id = b.id
                    WHERE `pg`.`seller_id` = '414336'),
   logs AS (SELECT `pl`.`product_id` AS `product_id`,
                   `pl`.`create_at` AS `create_at`,
                   `pl`.`price` AS `price`,
                   `pl`.`price_discount` AS `price_discount`,
                   `pl`.`rating` AS `rating`,
                   `pl`.`comment` AS `comment`,
                   `pl`.`order_count` AS `order_count`,
                   `pl`.`refund_count` AS `refund_count`,
                   `pl`.`quantity` AS `quantity`,
                   (pl.order_count * pl.price_discount) AS `revenue`,
                   row_number() over (partition by product_id order by create_at desc) AS `day`
            FROM `product_log` `pl`
            WHERE (pl.product_id in (select id from products))
              AND (`pl`.`create_at` BETWEEN 1656702000 AND 1661886000))
   SELECT `p`.`id` AS `id`,
          `p`.`id` AS `sku`,
          concat('https://www.wildberries.ru/catalog/', p.id, '/detail.aspx') AS `url`,
          `p`.`create_at` AS `create_at`,
          `p`.`name` AS `name`,
          coalesce(p.brand_id, '') AS `brand_id`,
          coalesce(p.brand_name, '') AS `brand_name`,
          coalesce(p.seller_id, '') AS `salesman_id`,
          coalesce(p.salesman_name, '') AS `salesman_name`,
          coalesce(p.salesman_ogrn, '') AS `salesman_ogrn`,
          coalesce(p.salesman_trademark, '') AS `salesman_trademark`,
          `p`.`revenue_lost` AS `revenue_lost`,
          (select price_discount from logs where product_id = p.id and day = 1) AS `price`,
          (select round(avg(rating)) 
           from logs 
           where product_id = p.id 
             and rating &gt; 0 
             and create_at between 1659294000 and 1661886000) AS `rating`,
          (select comment 
           from logs 
           where product_id = p.id 
             and day = 1) AS `review_count`,
          (select quantity 
           from logs 
           where product_id = p.id 
           and day = 1) AS `in_stock`,
          (select round(AVG(price_discount))
           from logs
           where product_id = p.id
             and create_at between 1659294000 and 1661886000) AS `price_average`,
          (select sum(order_count)
           from logs
           where product_id = p.id
             and create_at between 1659294000 and 1661886000) AS `order_value`,
          (select sum(order_count)
           from logs
           where product_id = p.id
             and create_at between 1656702000 and 1659294000) AS `order_prev`,
          (select sum(revenue)
           from logs
           where product_id = p.id
             and create_at between 1659294000 and 1661886000) AS `revenue_value`,
          (select sum(revenue)
           from logs
           where product_id = p.id
             and create_at between 1656702000 and 1659294000) AS `revenue_prev`,
          (SELECT GROUP_CONCAT(order_count)
           FROM (SELECT order_count 
                 FROM logs 
                 WHERE product_id = p.id 
                 LIMIT 30) as order_chart
          ) AS `order_chart`,
          (select IF(COUNT(*) &gt; 30, 30, COUNT(*)) AS c
           from logs
           where product_id = p.id
             and create_at between 1659294000 and 1661886000
             and quantity &gt; 0) AS `was_on_sale_for_month`
   FROM `products` `p`
   ORDER BY `revenue_value` DESC
  </code></pre>