Slow running Magento queries killing site

[manikandan_r1]

Hi,

Am running a Magento site.

When the below query runs my site will be down again and again.

INSERT INTO `search_tmp_5b43c01db2bbd2_87967453` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, ((0) + LEAST((MATCH (data_index) AGAINST ('Garagon con agua*' IN BOOLEAN MODE)), 1000000) * POW(2, search_weight)) 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 `cataloginventory_stock_status` AS `stock_index` ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0 WHERE (stock_index.stock_status = 1) AND (MATCH (data_index) AGAINST ('Garagon con agua*' IN BOOLEAN MODE))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC LIMIT 10000

Kindly check the query and help me to resolve this.

[srirammagi]

Assume you are  running community edition.

What is the version of Magento?

Did you update the latest patches?

S

[gelanivishal]

Hello @manikandan_r1,

If your website is on product server then please enable flat catalog. I can see that product collection is load by join query so that issue is generated.

• For enable flat catalog, Set Yes on Use Flat Catalog Category and Use Flat Catalog Product these field. You can find out screenshot

• After enable flat catalog, Please run flat indexes 

Let me know if you have any trouble.

--
If you've found one of my answers useful, please give "Kudos" or "Accept as Solution"