Very long search time

[sitte]

I have a problem with a very long search time (600 seconds) on my shop (27000 products, 9 domains - multi-shop, ps_search_index - 6 612 054 records, ps_search_word - 307 836 records). Dedicated server 32GB RAM. The database is clogged with queries such as:

SELECT DISTINCT si.id_product FROM ps_search_word sw LEFT JOIN ps_search_index si ON sw.id_word = si.id_word LEFT JOIN ps_product_shop product_shop ON (product_shop.`id_product` = si.`id_product`) WHERE sw.id_lang = 1 AND sw.id_shop = 1 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "search") AND product_shop.indexed = 1 AND sw.word LIKE '%test%'

In mysql the hang with status: "Copying to tmp table"

I tried reindexing but no result. Can anyone suggest what is causing this?

[sitte]

I found solution. When You use multistore website with large pruducts base then the part of LEFT JOIN ps_product_shop is the reason for the reduced performance of the sql query. I added index:

ALTER TABLE `ps_product_shop` DROP INDEX `sitte1`, ADD INDEX `sitte1` (`id_product`, `active`, `visibility`, `indexed`) USING BTREE;

and all runs smoothly.