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?

  • Hello Bilal, Have you got the solution on the same? – Vaibhav Shahu Feb 27 '18 at 11:22
  • create index resolved the issue – Bilal Usean Feb 27 '18 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? – Vaibhav Shahu Feb 28 '18 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); – Bilal Usean Feb 28 '18 at 12:43

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, privacy policy and cookie policy

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