I have a Magento2 application and it has around 3637 products. The problem is that the catalog>products page on the admin side is loading very slowly, it takes around 4 minutes to load the page. So checking the logs on the server I found that the count query is taking too much time to execute.
SELECT COUNT(*) FROM (
SELECT `e`.*, `at_qty`.`qty`,
(SELECT GROUP_CONCAT(DISTINCT(ccev.value)) AS `category_value` FROM `catalog_category_entity_varchar` AS `ccev`
INNER JOIN `catalog_category_product` AS `ccp` WHERE (ccp.category_id = ccev.entity_id AND (ccp.product_id=e.entity_id) AND ccev.attribute_id='45')) AS `categories`,
(SELECT MAX(DISTINCT(lsoi.created_at)) AS `item_last_purchase` FROM `sales_order_item` AS `lsoi` WHERE (lsoi.product_id=e.entity_id)) AS `last_purchase`,
COALESCE(SUM(`soi`.`qty_ordered`), 0) AS `qty_sold`,
IF(`at_qty`.`qty` < 1, 1,0) AS `low_stock`,
`at_quantity_and_stock_status`.`is_in_stock` AS `quantity_and_stock_status` FROM `catalog_product_entity` AS `e`
LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1)
LEFT JOIN (SELECT `csoi`.`qty_ordered`, `csoi`.`sku`, `csoi`.`product_id` FROM `sales_order_item` AS `csoi`
LEFT JOIN `sales_order` AS `so` ON csoi.order_id = so.entity_id WHERE (so.status IN (''))) AS `soi` ON (e.sku = soi.sku) OR (e.entity_id = soi.product_id)
LEFT JOIN `cataloginventory_stock_item` AS `at_quantity_and_stock_status` ON (at_quantity_and_stock_status.`product_id`=e.entity_id) AND (at_quantity_and_stock_status.stock_id=1) GROUP BY `e`.`entity_id`
) AS `t`;After analyzing I have found that the issue is due to sales_order_item table query which is having around 60000 rows. So can I remove that from the query to improve performance as it is not required for the count? Or is there any other way to improve performance? Which file I need to edit for changing the query?
