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.