I noticed that search is very slow (or has become slow) for multi word queries - eg. if I search for [valve] on {link saved} the search time is fairly quick (1-2 sec) but if I search for [valve cap] its slows right down sometimes to minutes. (It seems very variable).
It there any way I can diagnose or speed this up?
The database is running on an 8GB, four CPU dedicated server with very little additional load.
I've got approximately 300 feeds and 1000,000 items (which is key to our business model - we need that many)
They were being updated daily by a cron but until we solve the speed issues I've phased the updates over a weekly period.
Hi Mark,
wow - if you mean 1 million items there that really is starting to push the boundaries of MySQL, and especially most shared hosting platforms. With so many products, I would normally recommend splitting the site into categorised sub-directories, including if required a search box at the top level from which the user can select the desired category... more information in this thread:
http://www.pricetapestry.com/node/205
Also see this thread for MySQL configuration for very large sites; although your options will be limited of course with shared hosting...
http://www.pricetapestry.com/node/732
I'll take a look at your search.php now...
Cheers,
David.
The actual database is on a high end "dedicated" server - shared with a Zen cart store which a the moment is not using a lot of CPU or memory.
Hi Mark,
That makes more sense. I've just followed up to your email...
Cheers,
David.
Hi Mark,
The situation in your example, where one of the multiple words is less than or equal to 3 characters, the full text index cannot be used, so the script uses a very basic search method which involves a full table scan for every word in the query (to see the difference, try "valve caps", which is very fast again).
I notice that you are running MySQL 5.0, which means that you may be in a position to configure the Full Text index to index words of 3 (or less) characters. Full details are on the following page, but of course this relies on access to your server's configuration which may not be available on shared hosting - although a dedicated server of course you can setup MySQL as required...
http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html
With ft_min_word_len adjusted, a corresponding change would need to be made in search.php in the code that decides which search method to use. Look for the following code on line 69:
if (strlen($word) <= 3)...and change the number 3 so that the test corresponds to your new value for ft_min_word_len. In other words, if you set ft_min_word_len = 3, the above code would need to be change to:
if (strlen($word) <= 2)(don't forget to re-import all feeds for the index to be rebuilt)
If the above isn't an option; in the first instance, if you could email me your current search.php and let me know the approximate number of products you have on your site i'll have a think about possible optimisations for you to try...
Cheers,
David.