Jump to content

Very long search time


Recommended Posts

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?

Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...