Hi David
I have read the posts about speed issue solutions.
I was hoping I could just remove the "GROUP BY name" in the search.php file.
I expected it to work, just without the items sorting... but no items are displaying at all. (I edited line 45)
Do you have an explanation why I couldn't do this?
Better yet, do you have a suggestion how to do this?
Below are the differences in query time without the GROUP BY for 175,000 records with the category of 'Shoes'
SELECT * FROM `products` WHERE category='Shoes' GROUP BY name LIMIT 0,10;
>>Showing rows 0 - 9 (10 total, Query took 7.6780 sec)
SELECT * FROM `products` WHERE category='Shoes' LIMIT 0,10;
>>Showing rows 0 - 9 (10 total, Query took 0.0007 sec)
Thank You for you time!!!
Lang
Thanks David!
I'm going to add more products into other categories over the next few days to see how it handles with 1,000,000+ records.
I'll let you know if I have success, or if I end up installing the script multiple times into sub-directories.
Lang
Hi Lang,
If you have not come across it already, check out this thread for lots of discussion about MySQL configuration for very large sites...
http://www.pricetapestry.com/node/732
Cheers,
David.
Hi David,
Just an update... I decided it best not to put the 1,000,000+ records into one installation.
I'm still in the process of installing the script multiple times into sub-directories.
Lang
Hello Lang,
Wow - that is a lot of products for a single installation! The modified SQL will be failing because the summary components require a GROUP BY clause. Without the GROUP by, the line could be modified as follows:
$sql = "SELECT * , price AS minPrice, price AS maxPrice, 1 AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where;Hope this helps!
Cheers,
David.