Flat Tables in Magento

Flat Tables vs EAV

Flat tables denormalize EAV data into a single table per entity, optimized for reads:

-- EAV requires multiple JOINs
SELECT p.entity_id, v.value AS name, d.value AS price
FROM catalog_product_entity p
JOIN catalog_product_entity_varchar v ON v.entity_id = p.entity_id AND v.attribute_id = 71
JOIN catalog_product_entity_decimal d ON d.entity_id = p.entity_id AND d.attribute_id = 75;

-- Flat table: single table, no JOINs
SELECT entity_id, name, price
FROM catalog_product_flat_1
WHERE entity_id = 1;

Product Flat Table Structure

-- Auto-generated flat table per store view
CREATE TABLE catalog_product_flat_1 (
    entity_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) DEFAULT NULL,
    sku VARCHAR(255) DEFAULT NULL,
    price DECIMAL(12,4) DEFAULT NULL,
    status SMALLINT DEFAULT NULL,
    visibility SMALLINT DEFAULT NULL,
    type_id VARCHAR(32) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (entity_id),
    INDEX IDX_NAME (name),
    INDEX IDX_SKU (sku),
    INDEX IDX_PRICE (price),
    INDEX IDX_STATUS (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The flat table contains all searchable/listable attributes in columns, making reads extremely fast.

Enable Flat Tables

<!-- app/code/Vendor/Module/etc/config.xml -->
<config>
    <default>
        <catalog>
            <flat>
                <enabled>1</enabled>
                <category_enabled>1</category_enabled>
            </flat>
        </catalog>
    </default>
</config>

Or via Admin: Stores > Configuration > Catalog > Catalog > Use Flat Catalog Product

Reindex Flat Tables

# Reindex product flat data
bin/magento indexer:reindex catalog_product_flat

# Reindex category flat data  
bin/magento indexer:reindex catalog_category_flat

# Check index status
bin/magento indexer:status

When Flat Tables Are Updated

Product saved → Flat table updated
Attribute added → Flat table restructured
Category moved → Flat table reindexed
Full reindex → All flat tables rebuilt

// Programmatic flat table rebuild
$flatResource = $objectManager->get(
    \Magento\Catalog\Model\ResourceModel\Product\Flat\Indexer::class
);
$flatResource->rebuild($storeId);

Flat Table Limitations

• Only stores attributes in the default attribute set
• Custom attributes must be added to the flat table
• Maximum columns: MySQL has a practical limit of ~4000 columns
• Not suitable for highly custom catalogs with hundreds of attributes

Category Flat Structure

CREATE TABLE catalog_category_flat_1 (
    entity_id INT UNSIGNED NOT NULL,
    parent_id INT UNSIGNED DEFAULT 0,
    name VARCHAR(255) DEFAULT NULL,
    is_active SMALLINT DEFAULT NULL,
    position INT DEFAULT NULL,
    level INT DEFAULT NULL,
    children_count INT DEFAULT NULL,
    url_key VARCHAR(255) DEFAULT NULL,
    url_path VARCHAR(255) DEFAULT NULL,
    image VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (entity_id),
    INDEX IDX_PARENT_ID (parent_id),
    INDEX IDX_IS_ACTIVE (is_active),
    INDEX IDX_URL_KEY (url_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Category Tree with Flat Table

-- Fast category tree loading
SELECT entity_id, parent_id, name, level, children_count
FROM catalog_category_flat_1
WHERE is_active = 1
ORDER BY position;

+-----------+-----------+---------------+-------+---------------+
| entity_id | parent_id | name          | level | children_count |
+-----------+-----------+---------------+-------+---------------+
|         2 |         1 | Default       |     1 |              5 |
|         3 |         2 | Electronics   |     2 |              3 |
|         4 |         2 | Clothing      |     2 |              2 |
|         5 |         3 | Phones        |     3 |              0 |
|         6 |         3 | Laptops       |     3 |              0 |
+-----------+-----------+---------------+-------+---------------+

Performance Comparison

// EAV query: ~50ms
$categories = $categoryCollection
    ->addAttributeToSelect(['name', 'url_key', 'image'])
    ->addFieldToFilter('is_active', 1)
    ->load();

// Flat table query: ~5ms
$categories = $flatCollection
    ->addFieldToFilter('is_active', 1)
    ->load();

// 10x performance improvement for read-heavy operations

When to Use Flat Tables

USE flat tables when:
  ✓ Product catalog is read-heavy
  ✓ Few custom attributes (< 200)
  ✓ Single-store or few store views
  ✓ Listing/search performance is critical

AVOID flat tables when:
  ✗ Highly custom catalog with 200+ attributes
  ✗ Multi-store with different attribute sets per store
  ✗ Frequent attribute additions/removals
  ✗ Heavy write operations

Optimizing Flat Tables

-- Add custom attribute to flat table
ALTER TABLE catalog_product_flat_1
    ADD COLUMN eco_friendly SMALLINT DEFAULT NULL,
    ADD INDEX IDX_ECO_FRIENDLY (eco_friendly);

-- Remove unused columns to reduce table size
ALTER TABLE catalog_product_flat_1
    DROP COLUMN old_attribute;

-- Check flat table size
SELECT 
    table_name,
    ROUND(data_length/1024/1024, 2) AS data_mb,
    ROUND(index_length/1024/1024, 2) AS index_mb,
    table_rows
FROM information_schema.tables
WHERE table_name LIKE 'catalog_product_flat_%';

Monitoring Flat Table Health

# Check if flat tables are up to date
bin/magento indexer:status catalog_product_flat

# Force rebuild if corrupted
bin/magento indexer:reindex catalog_product_flat --force

# Check for missing attributes in flat table
bin/magento catalog-flat:check

# Enable logging for flat table operations
bin/magento deploy:mode:set developer

Common Issues