Magento 2 MSI and the inventory_stock_2 table – the silent killer of indexing performance

In one of our recent Magento 2 implementations (approx. 200,000 products, Multi Source Inventory enabled) we noticed very long module indexing times:

• Inventory
• Product Price
• Category Products

All classic indexes were correct. MariaDB had 256 GB of RAM, the buffer pool was reasonable, EAV indexes were attached, and yet heavy queries related to:

inventory_stock_2

After a deeper analysis, it turned out that the problem was not the amount of data itself, but the way Magento MSI creates the inventory_stock_* table.

Where does the inventory_stock_2 table come from?

The inventory_stock_2 table appears in Magento after enabling Multi Source Inventory (MSI). Magento creates a separate table for each stock:

• inventory_stock_1
• inventory_stock_2
• inventory_stock_3 ...

The table is created dynamically by the class:

Magento\InventoryIndexer\Indexer\IndexStructure

and looks something like this:

CREATE TABLE inventory_stock_2 (
  sku varchar(64) NOT NULL,
  quantity decimal(12,4) NOT NULL DEFAULT 0.0000,
  is_salable tinyint(1) NOT NULL,
  PRIMARY KEY (sku),
  KEY index_sku_qty (sku, quantity))
 ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

What is the problem?

There are two key problems:

1️⃣ Lack of utf8mb4

The sku column is often created in utf8_general_ci, while in catalog_product_entity we have:

utf8mb4_general_ci

This causes:

• character conversion during JOIN
• use of join buffer
• worse query plans

2️⃣ No index starting with is_salable

Magento very often performs queries such as:

WHERE stock_index.is_salable = 0 
OR stock_index.is_salable IS NULL

And since the only index is:

(sku, quantity)

MariaDB cannot effectively filter by is_salable.

The result?

• full table scans
• long DELETE during price index
• lock wait timeout 1205
• inventory indexer "processing" endlessly

Why does manual ALTER TABLE not work?

Because Magento does the following during each indexing:

• DROP TABLE inventory_stock_X
• CREATE TABLE inventory_stock_X

Every manual change disappears.

Therefore, the solution must be: automatic modification of the table after its creation.

Solution: Kowal_MsiStockFix module

Instead of modifying the Magento core, we create a plugin that, after creating the table:

• sets utf8mb4_general_ci
• adds an index (is_salable, sku)

Module structure

app/code/Kowal/MsiStockFix/
├── registration.php
├── etc/
│   ├── module.xml
│   └── di.xml
└── Plugin/
    └── IndexStructurePlugin.php

registration.php

<?php
use Magento\Framework\Component\ComponentRegistrar;

ComponentRegistrar::register(
    ComponentRegistrar::MODULE,
    'Kowal_MsiStockFix',
    __DIR__
);

etc/module.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Kowal_MsiStockFix" setup_version="1.0.0"/>
</config>

etc/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">

    <type name="Magento\InventoryIndexer\Indexer\IndexStructure">
        <plugin name="kowal_msi_stock_fix_index_structure"
                type="Kowal\MsiStockFix\Plugin\IndexStructurePlugin"
                sortOrder="10"/>
    </type>

</config>

Plugin: IndexStructurePlugin.php

<?php
declare(strict_types=1);

namespace Kowal\MsiStockFix\Plugin;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\InventoryIndexer\Indexer\IndexStructure;
use Magento\InventoryMultiDimensionalIndexerApi\Model\IndexName;
use Magento\InventoryMultiDimensionalIndexerApi\Model\IndexNameResolverInterface;

class IndexStructurePlugin
{
    private const CHARSET = 'utf8mb4';
    private const COLLATION = 'utf8mb4_general_ci';

    public function __construct(
        private readonly ResourceConnection $resource,
        private readonly IndexNameResolverInterface $resolver
    ) {}

    public function afterCreate(IndexStructure $subject, $result, IndexName $indexName, string $connectionName)
    {
        $connection = $this->resource->getConnection($connectionName);
        $resolvedName = $this->resolver->resolveName($indexName);
        $table = $this->resource->getTableName($resolvedName);

        if (strpos($table, 'inventory_stock_') === false) {
            return $result;
        }

        if (!$connection->isTableExists($table)) {
            return $result;
        }

        // 1. Ustaw utf8mb4
        $connection->query(
            sprintf(
                "ALTER TABLE `%s` CONVERT TO CHARACTER SET %s COLLATE %s",
                $table,
                self::CHARSET,
                self::COLLATION
            )
        );

        // 2. Dodaj indeks (is_salable, sku)
        $indexes = $connection->getIndexList($table);
        if (!isset($indexes['IDX_STOCK_SALABLE_SKU'])) {
            $connection->addIndex(
                $table,
                'IDX_STOCK_SALABLE_SKU',
                ['is_salable', 'sku'],
                AdapterInterface::INDEX_TYPE_INDEX
            );
        }

        return $result;
    }
}

Module installation

bin/magento module:enable Kowal_MsiStockFix
bin/magento setup:upgrade
bin/magento cache:flush
bin/magento indexer:reindex inventory

Effects after implementation

• No disappearing collation
• Faster DELETE with price index
• No lock wait timeout
• Inventory index ends correctly
• Fewer join buffers and full scans

Summary

The problem with the inventory_stock_2 table is not obvious, because everything looks "correct" and the indexes are green.

Only analysis of:

• query plans
• collation
• the MSI table recreate mechanism

allows you to find the real source of the problem.

If you have a store with a large number of products and use MSI, it is worth implementing a similar solution.

Magento is often fast. It just needs a little help 😉