7

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 ?

5

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.

3

In a broader and perhaps oversimplified sense, you might look at it like this:

  • MySQL indices are intended to speed up lookup inside a single column or table respectively

  • Magento indices are simply a means of caching data in a single table that belongs together in order to avoid JOINs and the like

Given this point of view, your custom module should use an "index" aka caching table if it's prone to expensive JOINs. Otherwise, you can usually get away easily with MySQL indexing or even none at all. When using your own index table, you'll have to take its maintenance into account, though.

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.