I imported a feed that apparently has 1.8 million products. The import went well however, I am not able to view any of the product pages from this feed without a timeout leading to 404 not found.
Is there a way to optimize MYSQL to make it much faster and more efficient? I found something that claims to make MYSQL blazing fast and was wondering if you had any experience with it?
This is supposed to be a drop in replacement for MYSQL https://www.percona.com/software/mysql-database/percona-server
One other thing I noticed is that when I clicked on the merchant page it displayed the first page of products for this merchants and because I am using the sort by discount mod every product on the page has a price of 0.00. Any idea why this would be when I am using the Sale Price mod you provided in my other post.
P.S. I am going to reach out to my web host to see what their thoughts are about using percona.
The problem is if all the products are from the same merchant.
The SQL sentence for viewing all the products from that merchant is going to collpase the buffer, that is why you get timeout.
I also have a merchant with thousounds of products.
My solution was to split that merchant into categories, or if you want, you may not list this merchant into A-Z.
Note that for this reason, there is no sort by default for the index search results (merchant / category / brand) however that seems to be OK in this case, it's the product page that is taking longer to generate however this is more likely to be down to related products as the main product / price comparison query is a straight forward SELECT against an indexed field (normalised_name). David - if you wanted to check this, you could try temporarily disabling Related Products by changing line 10 of config.php as follows;
$config_useRelated = FALSE;
I note that you talk about: sort_buffer_size and key_buffer_size.
How about innodb_buffer_pool_size ?
Price Tapestry uses the default MyISAM storage engine so innodb settings wouldn't apply...
Check out this thread for MySQL configuration advise for working with large databases - in particular, significantly increasing sort_buffer_size and key_buffer_size as the MySQL defaults are very small!
Bear in mind that whilst Price Tapestry does not impose any limitations, there will of course be some point at which you reach the performance limitations of your hosting platform / MySQL configuration. You might want to consider splitting your site into multiple sub-directory or sub-domain installations - check out the last comment in this thread regarding searching across multiple sub-directory installations...