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?
create index idx_catalog_product_flat_1_visibility on catalog_product_flat_1 (visibility);