The Product Attributes index is an important part of Magento's category filtering system. This post provides an overview of what it is and how it works.

Layered navigation and the Product Attributes index

Magento's layered navigation allows customers to filter categories by whatever attributes you've set up. Customers can filter down to red t-shirts that come in size L, or only phones with more than 16GB of storage that fit into their budget. The issue with allowing this flexibility is that the product attribute data that's being filtered on is stored in Magento's EAV (entity-attribute-value) database tables. The EAV data model allows Magento to efficiently store a wide range attribute data for different types of products, but a downside is that it's harder to query than traditional flat database tables. To solve this problem, Magento uses the Product Attributes index.

The Product Attributes index (known internally as the EAV index) stores a condensed version of each product's filterable attribute values. Values are stored separately for each store, as EAV attributes can have different values in different stores. Only attributes that are used in either category or search layered navigation are included in the index, which means only select, multiselect and price attributes can be included. Select and multiselect attributes have their index values stored in the catalog_product_index_eav database table, while price attribute values are stored in the catalog_product_index_eav_decimal table. These index tables are then used when Magento wants to filter lists of products by an EAV attribute, rather than having to try and use the main EAV tables.

The EAV index is updated whenever a product is saved. If the product has any parents (i.e. it's associated with a bundle or configurable product) or any children (i.e. it is a bundle or configurable product), EAV index entries are also updated for the parent/child products.

Filtering and complex products

The other main purpose of the EAV index is to handle complex products, like configurable or bundle products. These products have multiple child products that the customer can choose between. For example, a configurable t-shirt product that's configurable on both colour and size might have four child products:

SKUColourSize
Parent configurable product
config-01--
Child simple products
simple-01RedSmall
simple-02RedLarge
simple-03GreenSmall
simple-04GreenLarge

This configurable product would allow a customer to select a colour and a size when adding it to their cart. As the parent t-shirt product is configurable on colour and size, it can't have any values for these attributes itself. This would mean if it was in a category and a customer filtered for red products, it wouldn't appear in the list despite one of its options being red.

To handle this, Magento generates EAV index entries for complex products based on their children. The example configurable product above would have EAV index entries for the colour attribute saying it was both red and green, and entries for the size attribute saying it was both large and small. This means if the customer filters on any of these colours or sizes, the configurable product will be shown. An example of these index entries is shown below. Note that the index data is actually stored differently, using numeric product, attribute and value IDs. The example below is just to give an idea of what type of data is in the EAV index:

SKUAttributeValue
config-01ColourRed
config-01ColourGreen
config-01SizeSmall
config-01SizeLarge

What happens if the index is incomplete?

The EAV index is used to power the layered navigation system on category and search result pages. This means if the EAV index is missing or incomplete, the layered navigation filters on these pages will be missing some options or completely absent. One way this could happen is if a new store is added. Magento stores EAV index entries per store, so when a store is first added there won't be any index entries for it. In this case, the EAV index would need to be manually reindexed.

On a site with a lot of products and attributes, reindexing the EAV index can take some time. We recommend that this is done from the command line (using the indexer.php script) rather than via the Magento admin panel. Depending on your web server configuration, if the reindexing process takes too long it may time out and be terminated if it's run from the admin panel. Running the reindexing process from the command line avoids this issue.

The Product Attributes index may not be as critical to your store as some other indexes (such as Category Products or Product Prices), but it plays an important role in allowing category filtering. Hopefully this post has given you a better idea of how it works.