Magento 1: purpose of Magento index tables vs MySQL indexes

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.

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.