2

When I check SHOW PROCESSLIST; in mysql I got below query. It heavily uses CPU (more than 100%). We have a separate server for mysql(64GB RAM).

INSERT INTO `search_tmp_598075de5c7e67_73335919` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, (((0)) * 1) AS score FROM `catalogsearch_fulltext_scope1` AS `search_index` LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id LEFT JOIN `catalog_category_product_index` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id LEFT JOIN `review_entity_summary` AS `rating` ON `rating`.`entity_pk_value`=`search_index`.entity_id AND `rating`.entity_type = 1 AND `rating`.store_id  =  1 WHERE (category_ids_index.category_id = 2299)) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC LIMIT 10000

Why it uses my full CPU resources?

Where I find the code to create the above query?

4
  • Hello Bilal, Have you got the solution on the same? Commented Feb 27, 2018 at 11:22
  • create index resolved the issue Commented Feb 27, 2018 at 11:34
  • Thanks for the reply. what do you mean by create index? have you run the magento reindex all or you have done something else? Commented Feb 28, 2018 at 12:37
  • 2
    create index to the column.... something like create index idx_catalog_product_flat_1_visibility on catalog_product_flat_1 (visibility); Commented Feb 28, 2018 at 12:43

2 Answers 2

1

For thos who are still looking for the solution below is the solution.

https://support.magento.com/hc/en-us/articles/360059449112-MDVA-21095-INSERT-INTO-search-tmp-never-ends-after-mass-attribute-update

Patch is located at https://github.com/magento/quality-patches/blob/magento_magento2_34529/patches/os/MDVA-21095_2.3.2.patch

0

Have a look at: https://github.com/magento/magento2/issues/15545

There are some great suggestions here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.