We have a site where the client needs a number of customers to have an account specific price. For this, we've used customer groups. The accounts, products, and pricing are all updated via an API - if an account price is specified then we create a customer group dynamically and then store the group_price on it.
The issue we have is that Magento's
catalog_product_price_index table stores a price for every product, customer group, and website ID in it (even if no group pricing exists). Presumably this is so that Magento only has to do a single look up to the price table which makes sense however this table is now massive and takes ages to create.
My immediate thought is that we rewrite the indexer so that:
- The NOT LOGGED IN customer group always has a row stored in the price index
- Any customer groups that have a group price specified for a product are stored in the price index
We would then modify the product so that it would look for the customer group price and fallback to the NOT LOGGED IN price if none was found.