I have a new site with 100k+ products and the performance is fine other than search pages for merchants with more than 20k products. It seems like the problem is related to the size of the list being processed, which makes me think the slowness is due to some of the special add-ons to the SELECT (ORDER BY, etc.). Can you think of a way to streamline the SELECTS to make these pages quicker? I tried to remove some of the additional stuff on the SELECT but I broke something. Which pieces would you think I could remove without any problems? Do you think that would help? I know I'll be giving up some features, but it's a trade-off I need to consider.
I'm thinking that I might clone search.php and make one called fastsearch.php that was built for speed. Or maybe add a parameter to the url to limit the fancy stuff on the SELECT. Do you see any merit to this? Google supposedly pays attention to site speed, so I would prefer not to have any pages that take 20-30 seconds to load.
Thanks for any ideas you can provide!
Gregor
That's great David! I just replaced my sql with what you suggested and now the biggest merchants are down to 1 second or less. Price and relevance searches are all very quick.
Thank you so much!
Gregor
Hi Gregor,
Had you come across this thread which discusses MySQL configuration for very large sites - increasing the key and sort buffer sizes (avoiding the need for temporary tables) can have a big impact. By default the merchant index is not sorted as the default sort type is "relevance" but that only applies to normal keyword searches; however there is of course an internal sort by name that takes place as part of the GROUP BY processing.
However, for a merchant index query (which are typically the largest / slowest for a very large site) there is actually no need for this GROUPing, so I have just tested an alternative method which should help significantly. In your search.php, look for the following code at line 108:
$sql = "SELECT SQL_CALC_FOUND_ROWS * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY name";...REPLACE that with:
if ($parts[0]=="merchant"){
$sql = "SELECT SQL_CALC_FOUND_ROWS * , price AS minPrice, price AS maxPrice, 1 AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere;
}
else
{
$sql = "SELECT SQL_CALC_FOUND_ROWS * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY name";
}
Hope this helps!
Cheers,
David.
--
PriceTapestry.com