Issue Task Closed

#51094SQL-Optimize the FAL

Tobias Klepp 08/15/13, 18:07 updated 10/02/18, 12:10

Description

In the process of entering a lot of data in the TYPO3 6.1 backend, I have some realy big FAL performance problems.

I programmed an extension, that shows a list of 10 records per page. Every record has an image that is connected as a reference. This simple SQL takes over 35 seconds, because the SQL optimizer can’t use an index for the two important fields “tablenames” and “fieldname” in the sys_file_reference table. If I have a lot of images in the system (over 5000), it takes a lot of time and makes the website unuseable. I created a new index on the sys_file_reference table with the following statement:

CREATE INDEX tablenames_fieldname_uids 
ON sys_file_reference (tablenames(50),fieldname(50),uid_foreign,uid_local);

This key improves the SELECT statement very much. The same simple SQL takes less than 1 second.

I think, the key length can be optimized. A better index would be a unique index, but it is possible to reference the same file more than once in an mn relation file field. I have no idea, if this functionality is needed. For me, it makes no sense to reference the same file in the same field more than once.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

Subtasks 3