Magento has many tables for managing prices
It looks to me that:
- in product/category pages the product prices are loaded from the catalog_product_flat tables.
- in search pages the _idx table are used ( I think for price range filter).
Table with data
| Table | Notes | |---------------------------------------------|------------------------| | catalog_product_index_price | has data - main table | | catalog_product_index_price_idx | has data | | catalog_product_index_price_tmp | has data |
Table without data
| Table | Notes | |---------------------------------------------|------------------------| | catalog_product_index_price_bundle_idx | no data | | catalog_product_index_price_bundle_opt_idx | no data | | catalog_product_index_price_bundle_opt_tmp | no data | | catalog_product_index_price_bundle_sel_idx | no data | | catalog_product_index_price_bundle_sel_tmp | no data | | catalog_product_index_price_bundle_tmp | no data | | catalog_product_index_price_cfg_opt_agr_idx | no data | | catalog_product_index_price_cfg_opt_agr_tmp | no data | | catalog_product_index_price_cfg_opt_idx | no data | | catalog_product_index_price_cfg_opt_tmp | no data | | catalog_product_index_price_downlod_idx | no data | | catalog_product_index_price_downlod_tmp | no data | | catalog_product_index_price_final_idx | no data | | catalog_product_index_price_final_tmp | no data | | catalog_product_index_price_opt_agr_idx | no data | | catalog_product_index_price_opt_agr_tmp | no data | | catalog_product_index_price_opt_idx | no data | | catalog_product_index_price_opt_tmp | no data |
So it looks like there only 3 tables that have data:
- catalog_product_index_price
- catalog_product_index_price_idx
- catalog_product_index_price_tmp
The only actual used table is catalog_product_index_price, layer navigation uses it to filter the products by price. (see Mage_Catalog_Model_Resource_Layer_Filter_Price->_getPriceExpression())
Tables: catalog_product_index_price
| entity_id | customer_group_id | website_id | tax_class_id | price | final_price | min_price | max_price | tier_price | group_price |
It hosts all combinations of websites/customer group My permutation math is not very good anyway suppose you have:
- 100.000 products
- 2 websites ( an the price attribute scope set to Website )
- 10 customer groups
=> 100.000 * 2 * 10 = 2.000.000 rows
If you don't use different prices for different customer groups you end up waisting a lot of space on DB and the price reindex gonna be very slow. (Because basically all 20 rows for each products will contain the same values )
Questions:
- It is safe to delete all the above empty tables ?
- Why there are 3 table catalog_product_index_price with data but only 1 is actually used ?
Optimization:
- It would be possible to reduce the amount of rows in the index table in case the product prices are all the same for all the combinations ?