I recently imported over 1 thousand client reviews. Right after importing categories and filters went extremely slow. I found the following SQL to be cause, but I cannot find how to change it to keep the reviews and get the site back to normal. Anyone can help ?

Thanks !

COUNT(DISTINCT e.entity_id),
ROUND(MAX(price_index.min_price ) * 1, 100) AS `max`,
ROUND(MIN(price_index.min_price ) * 1, 100) AS `min`,
STDDEV_SAMP(ROUND((price_index.min_price ) * 1, 100)) AS `std`
FROM `catalog_product_flat_1` AS `e`
 INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=13
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `cataloginventory_stock_status` AS `_inv` ON _inv.product_id = e.entity_id and _inv.website_id=0
 LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
WHERE (price_index.min_price IS NOT NULL);```
  • Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 4 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Mar 6, 2020 at 18:19
  • Could you post TEXT results of EXPLAIN (your query); ? Mar 10, 2020 at 17:58


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.