Magento – Magento 1: purpose of Magento index tables vs MySQL indexes

databaseindexmagento-1magento-1.9performance

I've been working with Magento for years and I feel like this is a stupid question but anyway, I feel like I need some clarifications.

As you may know, Magento 1 comes with several index tables such as (I won't list them all):

  • cataloginventory_stock_status_idx
  • catalog_category_anc_categs_index_idx
  • catalog_category_anc_products_index_idx
  • catalog_category_product_index
  • catalog_product_enabled_index
  • catalog_product_index_* tables
  • report_viewed_product_index

I know what is the purpose of an index, however I'm wondering how is it better to use index tables over MySQL indexes ? I'm wondering especially when should one use index tables in a custom module ?

Best Answer

Different indexes seem to have different purposes in Magento. It's not always equivalent of a mysql index. For instance, the index table: catalog_category_product_index holds the product/category associations along with some additional attributes like: visibility, position etc. This table is used by core Magento for pulling out products for product listing pages for specific categories on the front-end. Without this table, Magento would need to do a join either with the flat tables or with eav tables to pull all visible products assigned to a category.

Some of these index tables might be used as temp tables during indexing itself and each of them has a very specific purpose getting into the details of which is hard to contain within a limited scope.

Related Topic