Как ускорить сортировку в оконной функции?

Question

[Богдан]

Если использовать сортировку в оконной функции то очень медленно выполняется запрос, более 20 секунд на массиве данных в 5000 записей. Использую MySQL 8.0.22

Без сортировки 66 мс

select p.product_id as id, JSON_ARRAYAGG(JSON_OBJECT('image', pi.image)) AS images
from oc_product p 
left join oc_product_image pi ON pi.product_id = p.product_id
where p.status = 1 
group by p.product_id

с сортировкой более 20 секунд.

select p.product_id as id, 
JSON_ARRAYAGG(JSON_OBJECT('image', pi.image)) OVER (ORDER BY pi.sort_order) AS images
from oc_product p 
left join oc_product_image pi ON pi.product_id = p.product_id
where p.status = 1 
group by p.product_id

explain:

-> Window aggregate with buffering: json_arrayagg(json_object('image',pi.image)) OVER (ORDER BY pi.sort_order )   (actual time=12190.707..15578.358 rows=6541 loops=1)
    -> Sort: pi.sort_order  (actual time=51.189..52.606 rows=6541 loops=1)
        -> Table scan on <temporary>  (actual time=0.002..0.713 rows=6541 loops=1)
            -> Temporary table with deduplication  (cost=2996.38 rows=6540) (actual time=46.472..48.014 rows=6541 loops=1)
                -> Nested loop left join  (cost=2996.38 rows=6540) (actual time=0.029..32.562 rows=6915 loops=1)
                    -> Filter: (p.`status` = 1)  (cost=707.38 rows=6540) (actual time=0.016..17.518 rows=6541 loops=1)
                        -> Table scan on p  (cost=707.38 rows=6656) (actual time=0.014..16.289 rows=6656 loops=1)
                    -> Index lookup on pi using product_id (product_id=p.product_id)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=0 loops=6541)

индексы есть:

CREATE TABLE `oc_product_image` (
  `product_image_id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_image_id`),
  KEY `product_id` (`product_id`),
  KEY `oc_product_image_sort_order_IDX` (`sort_order`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=4986 DEFAULT CHARSET=utf8


CREATE TABLE `oc_product` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `model` varchar(64) NOT NULL,
  `sku` varchar(64) NOT NULL,
  `upc` varchar(12) NOT NULL,
  `ean` varchar(14) NOT NULL,
  `jan` varchar(13) NOT NULL,
  `isbn` varchar(17) NOT NULL,
  `mpn` varchar(64) NOT NULL,
  `location` varchar(128) NOT NULL,
  `quantity` int NOT NULL DEFAULT '0',
  `quantity_store_2` int NOT NULL DEFAULT '0',
  `stock_status_id` int NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `manufacturer_id` int NOT NULL,
  `shipping` tinyint(1) NOT NULL DEFAULT '1',
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `points` int NOT NULL DEFAULT '0',
  `tax_class_id` int NOT NULL,
  `date_available` date NOT NULL DEFAULT '0000-00-00',
  `weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `weight_class_id` int NOT NULL DEFAULT '0',
  `length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `length_class_id` int NOT NULL DEFAULT '0',
  `subtract` tinyint(1) NOT NULL DEFAULT '1',
  `minimum` int NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `viewed` int NOT NULL DEFAULT '0',
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY (`product_id`),
  KEY `oc_product_sku_IDX` (`sku`) USING BTREE,
  KEY `oc_product_status_IDX` (`status`) USING BTREE,
  KEY `oc_product_sort_order_IDX` (`sort_order`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8

Answer 1

[Rsa97]

А если вместо JSON получать простой список?

SELECT `p`.`product_id`, `pi`.`images`, ...
  FROM (
    SELECT `product_id`, 
           GROUP_CONCAT(`image` ORDER BY `sort_order`) AS `images`
      FROM `oc_product_image`
      GROUP BY `product_id`
  ) AS `pi`
  JOIN `oc_product` AS `p`
    ON `p`.`product_id` = `pi`.`product_id`
      AND `p`.`status` = 1

Answer 2

[Богдан]

Что еще интересно что через подзапрос LATERAL все летает

WITH
        tmpProduct AS (
          SELECT
            p.product_id as id,
            p.image,
            IF(count(t.image),
              JSON_ARRAYAGG(JSON_OBJECT('image', t.image)),
              JSON_ARRAY()) AS images
          FROM oc_product p
          LEFT JOIN LATERAL (
            SELECT image FROM oc_product_image pi
            WHERE pi.product_id = p.product_id AND p.image != pi.image
            ORDER BY pi.sort_order
          ) AS t ON true
          WHERE p.status = 1
          GROUP BY p.product_id
        )
        SELECT id, IF(image, JSON_ARRAY_APPEND(images, '$', image), images) AS images
        FROM tmpProduct