Flat Tables and Indexed Data

What the flat catalog and the index tables precompute, why Adobe no longer recommends flat, and why a change waits for an indexer.

Syntaxbin/magento indexer:reindex catalog_product_attribute catalog_product_price

Reading EAV is expensive, so Magento precomputes. An indexer is a process that reads the normalised tables and writes a denormalised copy that the storefront queries instead.

The index tables that matter

Indexer id	Writes
catalog_product_price	catalog_product_index_price
catalog_product_attribute	catalog_product_index_eav
catalog_category_product	catalog_category_product_index_store1
catalogsearch_fulltext	the OpenSearch or Elasticsearch index

The flat catalog is separate, and optional

The flat catalog is one wide table per store view, catalog_product_flat_1, with a column per attribute. It is built by the catalog_product_flat indexer and switched on at Stores > Configuration > Catalog > Catalog > Storefront. Its columns are the static attributes plus those marked used_in_product_listing or used_for_sort_by. It is off by default, and Adobe states that using a flat catalog is no longer a best practice and is known to cause performance degradation and indexing issues. Leave it off on 2.4.9.

Why your change has not appeared

Each indexer runs in one of two modes. Update on Save reindexes the affected row inside the save. Update by Schedule uses mview, a materialised-view emulation: MySQL triggers write changed ids into a changelog table named <view_id>_cl, and cron processes them later.

bin/magento indexer:status
bin/magento indexer:set-mode schedule catalog_product_price
bin/magento indexer:reindex catalog_product_attribute

Example

<?php
// app/code/Vendor/EavDemo/Console/Command/IndexState.php
declare(strict_types=1);

namespace Vendor\EavDemo\Console\Command;

use Magento\Framework\Console\Cli;
use Magento\Framework\Indexer\IndexerRegistry;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class IndexState extends Command
{
    private const WATCHED = [
        'catalog_product_attribute',
        'catalog_product_price',
        'catalogsearch_fulltext',
    ];

    public function __construct(
        private readonly IndexerRegistry $indexerRegistry,
        ?string $name = null
    ) {
        parent::__construct($name);
    }

    protected function configure(): void
    {
        $this->setName('vendor:eav:index-state');
        $this->setDescription('Report status and mode for the catalog indexers');
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        foreach (self::WATCHED as $indexerId) {
            $indexer = $this->indexerRegistry->get($indexerId);

            $output->writeln(sprintf(
                '%-28s %-8s %s',
                $indexer->getId(),
                $indexer->getStatus(),
                $indexer->isScheduled() ? 'schedule' : 'realtime'
            ));
        }

        return Cli::RETURN_SUCCESS;
    }
}

// bin/magento vendor:eav:index-state
// catalog_product_attribute    valid    schedule
// catalog_product_price        invalid  realtime
// catalogsearch_fulltext       valid    schedule

When to use it

A price change saved in the admin that the category page refuses to show.
Deciding whether to leave the flat catalog switched off during an upgrade to 2.4.9.
Working out which indexer to reindex after a bulk attribute import.

More examples

The loop after an attribute change

The order matters: schema and data first, then indexes, then cache. indexer:status is the question to ask before anything else, because an invalid index and a stale cache look identical from the storefront. The bare indexer:reindex is the shorthand for naming all of them, and it is the wrong habit to form on a production catalogue.

# Applied the data patch, and the filter is still missing from the sidebar.
bin/magento setup:upgrade

# Which indexers are stale, and in which mode?
bin/magento indexer:status

# Rebuild only what the change touched.
bin/magento indexer:reindex catalog_product_attribute catalogsearch_fulltext

# Shorthand: no argument means every indexer. On a large catalogue
# that is minutes to hours, so name the ones you need.
bin/magento indexer:reindex

# Finally, drop the cached pages and blocks.
bin/magento cache:flush

How mview knows a product changed

Read this file to find out what a given indexer watches. Every subscribed table gets MySQL triggers that write the changed entity_id into catalog_product_flat_cl, and the indexer later processes the ids above its stored version in the mview_state table. This is also why an UPDATE run straight against a value table in a database client does get picked up in schedule mode, while a TRUNCATE or a trigger-less bulk load does not.

<?xml version="1.0" encoding="UTF-8"?>
<!-- vendor/magento/module-catalog/etc/mview.xml (core, for reference) -->
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Mview/etc/mview.xsd">
    <view id="catalog_product_flat"
          class="Magento\Catalog\Model\Indexer\Product\Flat"
          group="indexer">
        <subscriptions>
            <table name="catalog_product_entity" entity_column="entity_id"/>
            <table name="catalog_product_entity_datetime" entity_column="entity_id"/>
            <table name="catalog_product_entity_decimal" entity_column="entity_id"/>
            <table name="catalog_product_entity_gallery" entity_column="entity_id"/>
            <table name="catalog_product_entity_int" entity_column="entity_id"/>
            <table name="catalog_product_entity_media_gallery_value" entity_column="entity_id"/>
            <table name="catalog_product_entity_text" entity_column="entity_id"/>
            <table name="catalog_product_entity_tier_price" entity_column="entity_id"/>
            <table name="catalog_product_entity_varchar" entity_column="entity_id"/>
        </subscriptions>
    </view>
</config>

Check whether flat is on before relying on it

isFlatEnabled() reads the configuration flag; isAvailable() also accounts for whether the index has been built. A product collection changes which table it queries based on this, so code that joins catalog_product_entity_varchar by hand can work on one installation and return nothing on another. Worth checking before you debug a collection you did not write.

<?php
// app/code/Vendor/Catalog/Model/FlatStatus.php
declare(strict_types=1);

namespace Vendor\Catalog\Model;

use Magento\Catalog\Model\Indexer\Product\Flat\State;

class FlatStatus
{
    public function __construct(private readonly State $flatState)
    {
    }

    public function describe(): string
    {
        // State::INDEXER_ID              = 'catalog_product_flat'
        // State::INDEXER_ENABLED_XML_PATH = 'catalog/frontend/flat_catalog_product'
        if (!$this->flatState->isAvailable()) {
            return 'flat catalog disabled: collections read EAV';
        }

        return 'flat catalog enabled: collections read catalog_product_flat_<store>';
    }
}

// Output: flat catalog disabled: collections read EAV

Discussion

Be the first to comment on this lesson.

The index tables that matter

The flat catalog is separate, and optional

Why your change has not appeared

Example

When to use it

More examples

The loop after an attribute change

How mview knows a product changed

Check whether flat is on before relying on it

Related

Discussion