Database Performance 3,6 million diffrent prices

[michel_wiel]

Hello guys,

I have a shop what have about 3,6 million diffrent prices have.

- 8387 products

- 1071 customergroups

- 3,6 million diffrent prices (Tier price)

So now ym looking for a out of the box solutions for a better performers from my database and my shop.

I hope someone can help me!

Best regards,

Michel

[JLHC]

If you are looking for an out of the box solution with no modifications on your end, you may need to look for caching system like LiteMage Cache or Varnish but they will mainly speed up the frontend but not the backend. 

What issues are you facing now? 

[michel_wiel]

Hello JLHC,

If you are looking for an out of the box solution with no modifications on your end, you may need to look for caching system like LiteMage Cache or Varnish but they will mainly speed up the frontend but not the backend. 
 
What issues are you facing now? 

When I reindex the product-price list it takes about 1,5 hours to reindex it als when I reindex it in SSH it's not working also when I add a product by API sometime the is not showing in the frontend and have a while it showing the product in the frontend.

Also when I reindex the product-price list and it's don I get a message that it needs again reindex the same product-price list it's only the product-price list gives those problems.

I hoop you can help me to make the database better performance. 

Best regards,

Michel

[Gabriel Guarino]

Hi Michel,

What Magento version is running your website?

Best regards.

Gabriel

[michel_wiel]

I running Magento version 1.9.2.1

[Gabriel Guarino]

My concern is that you have too many customer groups and that means that Magento is having a hard time displaying the price for each of your customers.

A similar scenario is found when there are too many attributes in an attribute set, that is why it's important to create attribute sets and have just the required attributes on the attribute sets.

On this case, a re-engineering of your database is highly recommended. That means that you should analyze what is the best way to organize your information on the database. Also, you should go through the templates related to prices and optimize them (catalog/product/price.phtml could be optimized), they have a lot of logic and you could keep just the logic that your store needs (on your custom theme, you should never modify the base theme).

[NirvanaServices]

That's how Magento works, the only solutions are better hosting or dynamic pricing, but the latter is something only companies like Amazon have which allow recalcs in near realtime so you don't maintain end customer pricing. Actually it's what our partners launch stores on, custom solution, but it's not available to retail as you need to adjust the business to the solution, not the other way around. You might want to consider a separate admin server for the reindex, but if you have multi-currency it's not going to work very well.