Task #54725

Epic #55070: Workpackages

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

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

Task #51094: SQL-Optimize the FAL

Add Indexes to sys_file_reference

Added by Ingo Schmitt over 5 years ago. Updated almost 2 years ago.

Status:
Closed
Priority:
Should have
Assignee:
Category:
Performance
Target version:
Start date:
2014-01-03
Due date:
% Done:

100%

TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
Sprint Focus:

Description

Szenario:
->Setup two 6.2 Installations with content, pages, images based on heimwerker.de and gartencenter.de
->Activated all logs
->worked in the backend
->called different pages in the fe via AP
->Analysed mysql_slow_log by mysqldumpslow (look into you mysql distribution)

...
Count: 6 Time=0.02s (0s) Lock=0.00s (0s) Rows=0.8 (5), root[root]@localhost
SELECT uid FROM sys_file_reference WHERE uid_foreign=N AND sys_file_reference.deleted=N AND tablenames='S' AND fieldname='S' AND sys_file_reference.t3ver_wsid=N AND sys_file_reference.pid!=-N ORDER BY so
rting_foreign

Count: 24 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.4 (9), root[root]@localhost
SELECT uid FROM sys_file_reference WHERE uid_foreign = N AND fieldname = 'S' AND tablenames = 'S' AND sys_file_reference.deleted=N

Count: 1765 Time=0.01s (21s) Lock=0.00s (0s) Rows=0.4 (732), root[root]@localhost
SELECT * FROM sys_file_reference WHERE tablenames='S' AND deleted = N AND hidden = N AND uid_foreign=N AND fieldname='S' ORDER BY sorting_foreign
...

Solution should be to add indices to the table sys_file_reference
KEY: tablenames and filedname
KEY: deleted
KEY: uid_foreign

So only at the first query only one colums is not using a index.


Related issues

Related to TYPO3 Core - Bug #77618: Workspace PageTree can become super-slow Closed 2016-08-24
Blocked by TYPO3 Core - Task #54726: sys_file_reference.fieldname optimize columns Closed 2014-01-03

Associated revisions

Revision 6335a24a (diff)
Added by Ingo Schmitt over 5 years ago

[TASK] Adapt indexes of sys_file_reference

The backend and frontend fire several queries for referenced
files - files with metadata overlay as used in tt_content for
images and media field as well as in pages:media.
Analysing the queries for that some indexed should be added
to improve the query speed on large installations.

This patch adds indexed for the table/fieldname combination
as well es the uid of the record the file is attached to.

Resolves: #54725
Releases: 6.2
Change-Id: I229e27e382e8d0fdaf9bbc0b6729f90dac019e19
Reviewed-on: https://review.typo3.org/26626
Reviewed-by: Steffen Ritter
Tested-by: Steffen Ritter

History

#1 Updated by Gerrit Code Review over 5 years ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/26626

#2 Updated by Ingo Schmitt over 5 years ago

After Applying 54725 and 54726 my speed improvement by 100 cycles in AB was 10%

#3 Updated by Gerrit Code Review over 5 years ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/26626

#4 Updated by Gerrit Code Review over 5 years ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/26626

#5 Updated by Gerrit Code Review over 5 years ago

Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/26626

#6 Updated by Gerrit Code Review over 5 years ago

Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/26626

#7 Updated by Steffen Ritter over 5 years ago

  • Parent task set to #51094

#8 Updated by Gerrit Code Review over 5 years ago

Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/26626

#9 Updated by Ingo Schmitt over 5 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#10 Updated by Steffen Ritter over 5 years ago

  • % Done changed from 100 to 90

#11 Updated by Steffen Ritter over 5 years ago

  • Assignee set to Ingo Schmitt

#12 Updated by Steffen Ritter over 5 years ago

  • % Done changed from 90 to 100

#13 Updated by Riccardo De Contardi almost 2 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF