MySQL query INSERT INTO “search_tmp…” never ends after mass attribute update in Magento 2.3.0 Causes Server overload and site down

my mysql found this query very sluggish and slow, which is as follow:

    INSERT INTO `search_tmp_5d5af23c3c8911_81047940` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT `search_index`.`entity_id`, (((0)) * 1) AS `score` FROM `catalog_product_index_eav` AS `search_index`
INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = search_index.entity_id
INNER JOIN `inventory_stock_1` AS `stock_index` ON stock_index.sku = product.sku
INNER JOIN `catalog_category_product_index_store1` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id IN (75&&SlEEp(3)))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
LIMIT 10000

Please help me, how to resolve.

Thank you.

  • yes, Magento2 has an issue with search. using temp table search_tmp_* for relevant sort shorting. So, the best way to move catalog search to elastic search to resolve the issue. – Amit Bera Aug 20 '19 at 10:07

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.