TYPO3 Core

I'm maintaining a news website with intensive use of categories, which are assigned to pages. The amount of categories is not much, but some categories are assigned to a lot of pages. Some categories are assigned to over 10.000 pages.

When saving a page, the saving time can be up to 30 seconds, in which the editor has to wait until he/she can continue work.

This is due to the following:

The category references are stored in the table sys_refindex by the class ReferenceIndex. When adding or removing a reference from a page to a category, the DataHandler calls the ReferenceIndex to update the references for this certain category.

In the ReferenceIndex the following happens:

• Fetches all current references with the right table name and uid and puts the hash in the array $currentRelationHashes.
• Recalculates all references to the category with the uid and gives it a sorting integer according to the loading order the page has been found. This loading order defines the sorting integer for all these recalculated references. Since one is inserted somewhere, be it in the beginning, middle or end, all existing references after the new one get a loading order + 1. A hash is calculated with the loading order included.
• A comparison is made between the hashes in $currentRelationHashes and the recalculated references

Since a lot of recalculated references have got another sorting integer, hashes don't match anymore. Non existing hashes from the recalculated references are inserted in the database, hashes in $currentRelationHashes but not in the recalculated ones are deleted.

In our case it sometimes happens a new page-category reference is added somewhere at the beginning and 10.000 relations after that get a new sorting integer, which will cause 10.000 references deleted and 10.001 (including new reference) are inserted in the database.

Related to this issue is that the deleted references are put in an ->in() statement with 10.000 hashes. At one point the amount of characters put in the ->in() statement will exceed the max_allowed_packet value, causing errors.

This happens both in v8 and v9