Issue Bug Resolved

#109089Reference index scales very poorly, slows down saving records in backend and should use a lock

Thomas Prangenberg 02/24/26, 14:32 updated 08/31/26, 16:40

Description

The sys_refindex table is updated on every save from the backend and, depending on the number of relations between records, can dramatically slow down performance when saving records in the backend.

In our case, this is caused by some heavily used categories that are assigned to almost all records in multiple tables. While saving a record that has one or more categories assigned, the reference indexer iterates through all relations to each of these categories, individually fetches the related database record and compiles information about it into an array. With around 12.000 relations, each category would take up to 7 seconds, leading up to a minute of processing time while saving solely for updating the refindex when there were multiple categories assigned to a record.

I was able to implement a workaround by overwriting \TYPO3\CMS\Core\Database\ReferenceIndex and instead of fetching each record using BackendUtility::getReord() I change it to one query per table. This reduced the time taken while saving records by a factor of 10.

There is even a todo comment in that class pointing to the fact that this is a known issue and should be optimized.

I changed \TYPO3\CMS\Core\Database\ReferenceIndex::compileReferenceIndexRowsForRecord from this (beginning at line 594):

                if ($field->isType(TableColumnType::INLINE, TableColumnType::GROUP, TableColumnType::SELECT)
                    && $field instanceof RelationalFieldTypeInterface
                    && $field->getRelationshipType() === RelationshipType::ManyToMany
                ) {
                    foreach ($itemArray as $itemKey => $item) {
                        // Get rid of soft-deleted foreign records, those should not create refindex entries
                        // @todo: It would be better if RelationHandler would not return soft-deleted MM rows. Unsure
                        //        how to do that since RH only works on the MM table, but it could then also return
                        //        the full foreign row along the way.
                        // @todo: Expensive. This code could be optimized to fetch multiple records at once per foreign
                        //        table, or make RH return full foreign rows in some hopefully efficient way.
                        $foreignSideRecord = BackendUtility::getRecord($item['table'], (int)$item['id']);

into this

                if ($field->isType(TableColumnType::INLINE, TableColumnType::GROUP, TableColumnType::SELECT)
                    && $field instanceof RelationalFieldTypeInterface
                    && $field->getRelationshipType() === RelationshipType::ManyToMany
                ) {

                    $recordUids = [];
                    $records = [];
                    foreach ($itemArray as $itemKey => $item) {
                        $recordUids[$item['table']] = $recordUids[$item['table']] ?? [];
                        $recordUids[$item['table']][] = $item['id'];
                    }
                    foreach ($recordUids as $table => $uids) {
                        $query = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
                        $query->getRestrictions()->removeAll()->add(new DeletedRestriction());
                        $query->select('*')->from($table)->where($query->expr()->in('uid', $uids));
                        $rows = $query->fetchAllAssociative();
                        foreach ($rows ?: [] as $row) {
                            $records[$table][$row['uid']] = $row;
                        }
                    }

                    foreach ($itemArray as $itemKey => $item) {
                        // Get rid of soft-deleted foreign records, those should not create refindex entries
                        // @todo: It would be better if RelationHandler would not return soft-deleted MM rows. Unsure
                        //        how to do that since RH only works on the MM table, but it could then also return
                        //        the full foreign row along the way.
                        // @todo: Expensive. This code could be optimized to fetch multiple records at once per foreign
                        //        table, or make RH return full foreign rows in some hopefully efficient way.
                        $foreignSideRecord = $records[$item['table']][(int)$item['id']] ?? null;

So far this works, though I am a bit worried about side effects. The problem is, that overwriting the ReferenceIndex class via XClass is not a solid solution, partly because a lot of its scope is private. I can’t really rely on this workaround for long.

If this cannot be fixed in the near future, do you have any suggestions what to change otherwise to avoid this issue without overwriting parts of the core. Could updating the reference index be deferred to after saving is finished? Could one exclude the sys_category table from the reference index without causing issues in the frontend?

In addition for it being an annoyance for backend users, the fact that saving takes too long also introduces another problem: In some cases we encountered a problem when two backend users saved a record at the same time (before one saving process was finished). This would cause duplicate key violations when inserting rows to sys_refindex, because having multiple users update the reference index at the same time basically causes a race condition.

To avoid that, I also added a lock to the ReferenceIndexUpdater (which has the downside of it slowing down responses even more):

<?php

namespace Vendor\Package\XClass\Core\DataHandling;

use Vendor\Package\Utility\GeneralUtility;
use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
use TYPO3\CMS\Core\Authentication\BackendUserAuthentication;
use TYPO3\CMS\Core\Locking\LockFactory;
use TYPO3\CMS\Core\Locking\LockingStrategyInterface;

/**
 * Fixes an issue that caused a UniqueConstraintViolationException when inserting data into sys_refindex
 * when multiple users would save records simultaneously.
 */
class ReferenceIndexUpdater extends \TYPO3\CMS\Core\DataHandling\ReferenceIndexUpdater
{
    public function update(): void
    {
        $locker = GeneralUtility::makeInstance(LockFactory::class)->createLocker('XClass_ReferenceIndexUpdater');
        if ($locker->acquire(LockingStrategyInterface::LOCK_CAPABILITY_EXCLUSIVE)) {
            try {
                parent::update();
                $locker->release();

            } catch (UniqueConstraintViolationException $e) {

                $locker->release();
                /** @var BackendUserAuthentication $user */
                $user = $GLOBALS['BE_USER'] ?? NULL;
                if ($user) {
                    $user->writelog(5, 0, 2, null, $e->getMessage() . ': ' . $e->getQuery()->getSQL() . ' ' . serialize($e->getQuery()->getParams()), $e->getTrace());
                }

            } catch (\Throwable $e) {

                $locker->release();
                throw $e;

            }

        }
    }
}

Reviews 3

History 715

  1. Garvin Hicking commented and changed status New Needs Feedback <span data-processor="relativetime" data-value="2026-02-25T06:26:40+00:00" > 02/25/26, 06:26 </span>

    Would you like to enter your patch into gerrit (or github) so that we can see how tests react?

  2. Gerrit Code Review pushed patch set 1 for main to change 93035 status Needs Feedback Under Review <span data-processor="relativetime" data-value="2026-03-02T12:08:25+00:00" > 03/02/26, 12:08 </span>

  3. Thomas Prangenberg commented <span data-processor="relativetime" data-value="2026-03-02T12:09:46+00:00" > 03/02/26, 12:09 </span>

    Garvin Hicking wrote in #note-1:
    > Would you like to enter your patch into gerrit (or github) so that we can see how tests react?

    Sure, I pushed a change to gerrit that addresses both problems.

    However, I want to point out that I am not quite sure if this fix can be considered stable. The change introduces a rather large database query with potentially thousands of parameters in the IN constraint. Depending on the configuration of range_optimizer_max_mem_size in MySQL or MariaDB, this query could exceed the memory that’s available to the range optimizer, likely leading to a full table scan, which in the best case will just nullify this optimization but could maybe also even make it worse. I’m not sure though what the exact threshold is here and how many relations would be required for this to happen.

    Also, adding a lock exactly at this point might not be the best idea universally, because it also blocks requests that would not interfere with each other. It would be better to check for that first.

    The longer I look at this the more this looks like an architectural flaw to me. In my opinion it would be much more efficient to either:

    a) … update sys_refindex asynchronously – This could be done by introducing a new configuration value that prevents updating the refindex immediately and defers it to a scheduler task. Default behaviour could still be the current way.

    b) … exclude tables from the reference index that have too many relations. I know there exists an event for this, but I don’t know if it would be harmful to use it for tables that are configured via TCA.

    Of course both are only an option if a synchronized reference index is not mandatory. Unfortunately since I don’t fully understand exactly where the reference index is being used currently, I don’t really feel equipped to contribute a change here.

  4. Thomas Prangenberg commented <span data-processor="relativetime" data-value="2026-04-22T08:17:09+00:00" > 04/22/26, 08:17 </span>

    Just wanted to let you know that since we have changed this in our TYPO3 instance, we have opted to remove the lock and live with the fact that it sometimes will result in an exception.

    It’s a tradeoff. The lock ensures no duplicate keys but blocks saving of any record if another user is currently saving something else. We got complaints that his takes too long with a lot of concurrent users, so we removed it. Since the record is saved in any case, we just take the risk of an exception being thrown in <1% of cases.

    So maybe this should rather be a added as a configuration option at some point.

  5. Thomas Prangenberg commented and changed status Under Review Resolved done 0 % 100 % <span data-processor="relativetime" data-value="2026-08-31T16:40:13+00:00" > 08/31/26, 16:40 </span>

    Applied in changeset commit:218e889a4370d8f88a325bd48d88153ad2ff51d0.