cancel
Showing results for 
Search instead for 
Did you mean: 

Magento 2.3 admin product listing page is loading very slow

Magento 2.3 admin product listing page is loading very slow

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?

1 REPLY 1

Re: Magento 2.3 admin product listing page is loading very slow

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

Problem solved? Click Accept as Solution!