9

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 ?
Improve this question
1
  • 1
    As for all of the tables I can't answer but some such as catalog_product_index_price_bundle_x, catalog_product_index_price_cfg_x, and catalog_product_index_price_downlod_x will only have data if you have bundled, configurable and downloadable products inside the site. The others might be utilized for storing the final price with secondary currencies etc but on that I'm not positive. – Eirik Nov 30 '17 at 6:36
2

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
Improve this answer
5
  • sorry your answer is just saying nothing to me. – Fra Dec 4 '17 at 13:16
  • Can u please be more specific about what isn't clear? – Quisse Dec 4 '17 at 14:02
  • I know those table are index, I know you can truncate the index ( you are saying you can't but that is not true, you just need to reindex after ) you are saying nothing new .... if you don't have an answer to the question you should post a comment not an answer – Fra Dec 5 '17 at 9:45
  • Sorry for trying to help, have u even read my answer? U really need to place my answers next to your questions. – Quisse Dec 5 '17 at 10:55
  • Now I've done it for you. I hope we can have a constructive dialog to solve the question. – Quisse Dec 5 '17 at 11:03

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.