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.