Opening record in backend painfully slow with foreign table and large main table
Opening a record in the backend can become painfully slow under the following circumstances:
The main table (10.000 records) has got a 1:n relation to a foreign table (3 records). When trying to open the record of the main table in the TYPO3 backend, the following SQL statement is executed that often that it takes an exceptionally long amount of time to actually open the record.
SELECT * FROM `tx_myext_domain_model_foreigntable` WHERE (`uid` = ?) AND (`tx_myext_domain_model_foreigntable`.`deleted` = 0)
TCA of main table:
'attribute' => [ 'exclude' => true, 'label' => 'attribute', 'config' => [ 'type' => 'select', 'renderType' => 'selectSingle', 'foreign_table' => 'tx_myext_domain_model_foreigntable', ], ],
#2 Updated by SiteGefühl no-lastname-given 4 months ago
I can confirm the Problem in TYPO3 7 and PHP 7.1
We have something about 80.000 SQL queries after we clicked the edit button of the main record. It takes 3 minutes to open the record.
One identical query is executed something about 5000 times.
// Edit: Maybe this is going to fix the Problem? https://github.com/TYPO3/TYPO3.CMS/commit/f3e7a034c129aafc8b081e0125c50330207fae82#diff-ddfa1112aff05d23dcd52f1e19e04ba9
// If yes, I would be happy about a fix for Version 7
#6 Updated by Jakob Berlin 3 months ago
Not really confirming it but after disabling label_alt my edit view get back to work in a few seconds wich just CPU usage and not minutes with have DB usage. So it might be the cause of the problem.
Btw: It even happened when label is set to a foreign attribute of the foreign attribute itself. So, to have now troubles label should be a static value from the foreign table by now.
#7 Updated by Stefan P about 2 months ago
We are having this issue as well (with both, TYPO3 7 and 8). Our data structure is like this: products are the main table, products contain attributes via 1:n (irre, inline, so the product uid with index is inside the attributes table), And the attributes contain n:1 an attributelabel (attribute contains a field attributelabel holding a uid):
product ---1:n--- (field w/ index product) attribute (field label) --- n:1 --- attributelabel
ctrl => label in attributes is set to the field attributelabel - and this constellation triggers this behaviour. So, loading the inline label (or alt label) from a third table is responsible for this.