Maho's full reindex is now three to five times faster

On the sample data store, 1,040 products across 15 store views, a ./maho index:reindex:all used to take 5.2 seconds on SQLite and now takes 0.95. That is the kind of gain nobody notices until they look at the number, and that every catalog update feels afterwards. Pull request #1582 by fballiano was merged on 19 October 2026 and ships with 26.11.0.

What changed

The full reindex got faster on all three supported engines:

Engine	Before	After
SQLite	5.2 s	0.95 s
MySQL	4.54 s	1.63 s
PostgreSQL	5.58 s	1.09 s

Those are single runs per branch, timed by the author of the pull request on the sample store: no large catalog was measured. The figure that holds up better than the wall clock is the query count. A second full rebuild of the URL rewrites sends 545 queries instead of 17,079, and the search index 199 instead of 1,296. Every index table holds the same rows it held before.

The four levers

Planner statistics on SQLite. After a full reindex, Mage_Index_Model_Process::reindexAll() calls the new AdapterInterface::optimizeStatistics(): PRAGMA optimize on SQLite, ANALYZE of the given tables on PostgreSQL, nothing on MySQL. Without statistics, SQLite picked a plan for the EAV relation insert that read about 17 million rows, so that single statement was 3.2 of the 3.4 seconds of catalog_product_attribute. The stock filter of the index selects (Stock_Status::prepareCatalogProductIndexSelect()) is now an EXISTS instead of a join, which also fixes the plan when no statistics exist.

One run per index. index:reindex:all and the sample data installer iterate Runner::buildQueue(). The old loop built a new process collection, while reindexEverything() resolves dependencies through the indexer singleton, so the “already done” flag landed on a different object. catalogsearch_fulltext now declares its dependency on cataloginventory_stock, because the search rebuild reads the stock status.

Parallel lanes. Runner::buildLanes() splits the queue into groups of processes that depend on each other. On MySQL and PostgreSQL the command starts one child process per lane, and today there are four lanes: stock, attribute, price and search together, then URL, then category product, then tag. SQLite has a single writer, so it stays in series, and --sequential forces series everywhere:

# one index at a time, even on MySQL and PostgreSQL
./maho index:reindex:all --sequential

Diff-only writes. Mage_Index_Model_Resource_Abstract::syncData() deletes the main rows with no match in the _idx table, then upserts only the new rows and the rows where a column differs, compared with the new getNullSafeEqualsSql(). A full reindex of a catalog that did not change now writes almost nothing to the main tables.

The rest of the work

• URL rewrites: saveRewrite() skips a row that did not change, and refreshProductsRewrites() reindexes many products with one batch of queries per 250 products;
• pending events: Process::_processEventsCollection() collects up to 500 events the indexer accepts and reindexes them with one processBatch() call, which goes through reindexEntity(). Price, EAV, stock, category product, search and URL declare getBatchEntityIds(). An event that cannot join a batch, a delete or a category change for example, runs on its own;
• the search index: the rebuild reads the children of a whole batch of products, with the new catalog/search/index_batch_size setting, 500 instead of the fixed 100. It sits in the panel at Catalog > Catalog Search > Products per Search Index Batch;
• the schema: the price and EAV _idx and _tmp tables lose the single-column indexes no query uses, and catalog_product_entity gains an index on (type_id, entity_id);
• the index grid in System > Index Management and the index API ask hasUnprocessedEvents() for the “Update Required” flag, instead of loading every pending event just to count them.

The bugs that came along

The performance work surfaced five defects:

• after a mass product update, the price index could lose the tier and group prices of the updated products and of other products;
• on PostgreSQL and SQLite, a product not visible in one store removed the layered navigation index of every store;
• ./maho index:reindex:product did nothing for the search, category product and tag indexes;
• index:reindex:all rebuilt the stock index twice;
• on MySQL, two checkouts of two children of one bundle product could fail with a deadlock in the stock index.

The first one is the expensive one in production: a tier price that disappears from the index throws no error, it charges the wrong price.

What changes for scripts and custom code

Three points to check before upgrading:

• index:reindex:all prints the result of each index when its lane ends, not in code order. A script that reads its output line by line must allow for the new order, or pass --sequential;
• the command no longer stops at the first index that fails: it runs the others and exits with a failure code. A deploy that trusted the first error line must look at the exit code;
• the full price reindex holds the _tmp table lock from start to end. A product save in realtime mode, or a checkout that changes a stock status, waits until the reindex ends.

A third-party price type indexer that expects the old outer transaction still works: its own nested beginTransaction() now opens a real one.

When it lands

The pull request is merged into main on 19 October 2026, and the 26.11.0 milestone was still open when this article was written, so the code ships with that release. The reindex is not an administration detail: it is the step a store pays for on every import, every mass update and every new store view.

A reindex does not get fast in one heroic move. It gets fast by removing queries nobody asked for.

For the picture of the platform, with the changes the releases carry forward, the overview is on the Discover MahoCommerce page.