Task #51094


Epic #55070: Workpackages

Epic #54260: WP: FAL Missing Issues / Features / API

Story #54266: As an User I want FAL to be performant

SQL-Optimize the FAL

Added by Tobias Klepp almost 11 years ago. Updated almost 6 years ago.

Should have
File Abstraction Layer (FAL)
Target version:
Start date:
Due date:
% Done:


Estimated time:
(Total: 0.00 h)
TYPO3 Version:
PHP Version:
Sprint Focus:


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.

Subtasks 3 (0 open3 closed)

Task #54730: sys_file_processedfile.checksum shorten DB fieldClosedIngo Schmitt2014-01-03

Task #54726: sys_file_reference.fieldname optimize columnsClosedIngo Schmitt2014-01-03

Task #54725: Add Indexes to sys_file_referenceClosedIngo Schmitt2014-01-03


Related issues 1 (0 open1 closed)

Related to TYPO3 Core - Bug #51529: FAL is incredibly slow since all information are extracted from FS everytimeClosedSteffen Ritter2013-08-29

Actions #1

Updated by Philipp Gampe almost 11 years ago

  • Status changed from New to Accepted
  • Target version deleted (next-patchlevel)
  • TYPO3 Version changed from 6.1 to 6.0
  • Complexity set to medium

FAL guys need to have a look at the queries indexes.

Actions #2

Updated by Bernhard Kraft almost 11 years ago

Related: #51529

Actions #3

Updated by Steffen Ritter over 10 years ago

  • Subject changed from Improve FAL performance to Add index to sys_file_reference_table
  • Parent task set to #54266
Actions #4

Updated by Steffen Ritter over 10 years ago

  • Estimated time set to 0.50 h
  • Translation missing: en.field_remaining_hours set to 0.5
Actions #5

Updated by Steffen Ritter over 10 years ago

  • Subject changed from Add index to sys_file_reference_table to SQL-Optimize the FAL
  • Estimated time set to 0.00 h
Actions #6

Updated by Steffen Ritter over 10 years ago

  • Estimated time deleted (0.00 h)
Actions #7

Updated by Mathias Schreiber over 9 years ago

  • Status changed from Accepted to Resolved
  • Translation missing: en.field_remaining_hours deleted (0.0)


Actions #8

Updated by Benni Mack almost 6 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF