I am working on a store where i need to import products on a daily basis and the number of products may go up to more then 1,90,000. The Store discount heavily depends on the customer groups which are around 8800 including the core ones in number.

The Products are imported seamlessly, but when trying to reindex the Catalog Product Price index, it inserts 130 million records in catalog_product_index_price_final_idx table which takes huge time to complete.

Also the customer groups does not have any alternative. because catalog price rules, and tier prices depend heavily on them.

130 million records are a very huge amount of data on a mysql installations.

Note We have 15000 products as of now, but it is expected to increase to 1,90,000. so the number of records in catalog_product_index_price_final_idx table may got up to 1.65 billion records.

Note: The infrastructure of the server is optimized for high performance, with SSD for storage and MySQL configured using percona db.


Short answer - I don't think it's going to work.

I had a similar situation on a Magento 1 site but with 12,000 products on 2 websites and 1000+ groups. They also had a daily import which meant that the price index table reindexed daily. That was not fun and it's nowhere near the level you've got.

The way we got passed it was to ultimately reduce the number of groups. The client wasn't managing 1000 unique price list but rather the customers were assigned to 50 or so groups. Therefore we could add multiple customers to the group.

Are all 8800 groups unique or can the customers share a group?

If they are unique, I'd suggest creating a new layer of pricing rather than using customer groups. In M1 at least, the indexer is only used during collections (caveat: might be wrong) so you could just rewrite the product collection and final price methods to pull out your layer of pricing.

  • The customer groups are unique, They are the company names which has agents under them, so we have accommodated the agents as customers and the companies as Customer groups. And each company(Customer group) gets separate discount through catalog price rules, and bulk prices (tier prices). so we depend heavily on this configuration. – Taral Patoliya Nov 3 '16 at 11:24
  • Do all the customer groups have unique pricing? Or will several groups get the same discount? – Paul Nov 3 '16 at 11:40
  • It is possible that some customer groups may get same discount and pricing, need to look at the data though. However i have grouped the catalog rules by discount offered and customer groups, so yes the rules are applied to multiple customer groups for same discount and product. But for tier price it was not possible to include multiple customer groups for one tier price. – Taral Patoliya Nov 3 '16 at 12:48
  • The number of rules won't have an impact so much as the number of groups. The way we set things up, we added a new level to the account structure called a 'User'. When a user logged in, they logged into a customer account which then had the group assigned to it. It's quite a similar setup to what you have. How many catalogue price rules do you have setup? That should give some indication of how many groups you could potentially reduce it down to. – Paul Nov 3 '16 at 14:18
  • Initially we had some 5000 rules but we managed bring it down to 900 rules by grouping them using the discount persentages – Taral Patoliya Nov 3 '16 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.