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 about 6 years ago. Updated 12 months ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
File Abstraction Layer (FAL)
Target version:
-
Start date:
2014-01-03
Due date:
% Done:

100%

TYPO3 Version:
6.0
PHP Version:
5.3
Tags:
Complexity:
medium
Sprint Focus:

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.


Subtasks

Task #54730: sys_file_processedfile.checksum shorten DB fieldClosedIngo Schmitt

Task #54726: sys_file_reference.fieldname optimize columnsClosedIngo Schmitt

Task #54725: Add Indexes to sys_file_referenceClosedIngo Schmitt


Related issues

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

History

#1 Updated by Philipp Gampe about 6 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.

#2 Updated by Bernhard Kraft about 6 years ago

Related: #51529

#3 Updated by Steffen Ritter almost 6 years ago

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

#4 Updated by Steffen Ritter almost 6 years ago

  • Estimated time set to 0.50 h
  • translation missing: en.field_remaining_hours set to 0.5

#5 Updated by Steffen Ritter over 5 years ago

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

#6 Updated by Steffen Ritter over 5 years ago

  • Estimated time deleted (0.00 h)

#7 Updated by Mathias Schreiber over 4 years ago

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

fixed

#8 Updated by Benni Mack 12 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF