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_*
tablesreport_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.