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 8 years ago. Updated over 4 years ago.

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

100%

Estimated time:
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-slowClosed2016-08-24

Actions
Blocked by TYPO3 Core - Task #54726: sys_file_reference.fieldname optimize columnsClosedIngo Schmitt2014-01-03

Actions
#1

Updated by Gerrit Code Review over 8 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 8 years ago

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

#3

Updated by Gerrit Code Review over 8 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 8 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 8 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 8 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 8 years ago

  • Parent task set to #51094
#8

Updated by Gerrit Code Review over 8 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 8 years ago

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

Updated by Steffen Ritter over 8 years ago

  • % Done changed from 100 to 90
#11

Updated by Steffen Ritter about 8 years ago

  • Assignee set to Ingo Schmitt
#12

Updated by Steffen Ritter about 8 years ago

  • % Done changed from 90 to 100
#13

Updated by Riccardo De Contardi over 4 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF