#107422Load metadata alongside file data to avoid sub-queries and speed up performance
Description
I was wondering why typo3 does not implement a faster way to load a file and its metadata. Loading the file record first, then loading the metadata record in a separate query and overlaying the language in a third query seems very bad.
I just created a backend module to check metadata and came up with a query to do all in one.
here is how i did it for my use-case:
select f.*, null as sys_file_metadata, m.*
from sys_file f left join sys_file_metadata m on f.uid = m.file and m.sys_language_uid = 2
where f.storage = 1 and f.identifier like "/some/path/%" and (m.alternative is null or m.alternative = "")
All of this can be done with the doctrine query builder, so the a more simple use-case would work as well:
Load a file by uid (lets assume there is no overlay/fallback mechanism configured):
select f.*, null as sys_file_metadata, m.*
from sys_file f left join sys_file_metadata m on f.uid = m.file and m.sys_language_uid = :desired_language
where f.uid = :some_uid
It would be possible to split the resulting row into sys_file data and sys_file_metadata and pass both at once to the File constructor.
Or even when loading references
select r.*, null as sys_file, f.*, null as sys_file_metadata, m.*
from sys_file_reference r
join sys_file f on r.uid_local = f.uid
left join sys_file_metadata m on f.uid = m.file and m.sys_language_uid = :desired_language
where r.tablenames = :desired_table and r.uid_foreign = :desired_uid and r.sys_language_uid = :desired_language and r.hidden = 0 and r.deleted = 0
This would reduce the amount of queries hugely and therefore boost the performance big time.
I wanted to “hear your thoughts” about this. I know – there might be a better place for this, than the issue tracker but i wanted this idea to get SOME attention.
Reviews 0
No change resolves or names this issue in its commit message, and no comment links one.
History 4
-
Garvin Hicking commented and changed tracker
StoryTask statusNewNeeds Feedback <span data-processor="relativetime" data-value="2025-09-10T12:50:19+00:00" > 09/10/25, 12:50 </span> -
Philipp Wrann commented <span data-processor="relativetime" data-value="2025-09-10T13:54:38+00:00" > 09/10/25, 13:54 </span>
I don’t think using the query builder is a problem:
(untested code, but mine does pretty much the same)
$q = $connectionPool->getQueryBuilderForTable('sys_file'); $fileFields = ['f.uid as sys_file__uid', 'f.storage as sys_file__storage']; // generate for all fields from TCA $metaFields = ['m.uid as sys_file_metadata__uid', 'm.title as sys_file_metadata__title']; // generate for all fields from TCA $fields = [...$fileFields, ...$metaFields]; $q->select(...$fields) ->from('sys_file', 'f') ->leftJoin('f', 'sys_file_metadata', 'm', "f.uid = m.file AND m.sys_language_uid = $languageId"); ->where($q->expr()->eq('uid', $q->createNamedParameter($uid)));Workspaces should actually be possible but you would need to take care of it in the join clause. So i think this would be a lowlevel optimization for the ResourceFactory only but there it would make a big difference!
-
Garvin Hicking commented <span data-processor="relativetime" data-value="2025-09-10T16:15:38+00:00" > 09/10/25, 16:15 </span>
Please contribute a patch/diff if you feel this can be done, that makes it reviewable.
But circumventing the API for raw access like this is not a good architecture. Especially for real data objects that need to respect all kinds of record layers.
-
Georg Ringer commented and changed status
Needs FeedbackClosed <span data-processor="relativetime" data-value="2026-10-07T18:34:20+00:00" > 10/07/26, 18:34 </span>closing the issue because lack of feedback. if still relevant, please open a new issue or contact me on slack to discuss.
performance issues need to be really well thought and need to support every edge case. In a dedicated project this can be skipped, e.g. because no workspaces involved, … but if it should be in the core, it is not that easy
We cannot use raw SQL at this point because we have several APIs and query restrictions and further things to consider here. Which is also the reason we have layered access. Workspaces also need to be involved.
So sadly this naive approach might fit your use-case, but would be a lot tougher to unify considering all the possible options that we have.
If you are interested in trying to optimize the API to support one-shot loading of metadata, we’d be very happy for code contributions and can review that!