Magento 1.9: Product Price tables - how they work?

Question

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:

1. It is safe to delete all the above empty tables ?
2. Why there are 3 table catalog_product_index_price with data but only 1 is actually used ?

Optimization:

1. 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 ?

Answer 1

It is safe* to delete all the above empty tables ?

Those empty tables will be populated when Magento reindexes and the catalog has products for specified product type.
It should be safe to remove the _idx table Magento will re-create them during reindex.

Why there are 3 tables with data but only 1 is actually used ?

The idx table and tmp table are only used for indexing:

• _tmp is empty and it will be populated during reindex
• _idx is the actual index table