Magento 2.3 admin product listing page is loading very slow

[akhilesh4u66f7]

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?

[Bhanu Periwal]

Hello @akhilesh4u66f7 

Scheduling the reindexing process can be helpful here! Magento 2 reindexes all the products and categories when you save a product or category to update the index. During this process, the admin panel is going to go lethargic. Instead of carrying out reindexing each time, one can schedule the process with the below steps:

1. Login to admin panel

2. Navigate to System > Index Management

3.Using mass actions

You can reindex by cli as well:
php bin/magento indexer:reindex

[WebDevelopers]

I'll restate an answer I provided on another post that may help someone in the future, and that is you may need to reduce the number of product attributes that are available in the Columns section. To do this, locate and click on any superfluous attributes in Stores > Attributes > Product, open the Advanced Attribute Properties section, and change "Add to Column Options" to No. Do this for every attribute that you do not anticipate needing to see in the columns for the time being. Our product listing page went from 40-120 seconds down to 2.