Bug #54923

Increase SQL performance for sys_file_storage

Added by Ingo Schmitt almost 6 years ago. Updated about 2 years ago.

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

100%

TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

The BE Userauth is issuing the following SQL Query without any Index. Although the result is normally fetched pretty fast, it won't be stored in the Query cache, since no index is used.

SELECT * FROM sys_file_storage WHERE 1=1 AND sys_file_storage.hidden=0 AND sys_file_storage.deleted=0;

Solution 1: Add combined key to hidden and deleted

Also fields in the SQL don't match the TCA

name, tinytext. TCA Char 30
processingfolded, tinytext, TCA char 20

Solution 2: Change the Field definition

Associated revisions

Revision 85edd0e1 (diff)
Added by Ingo Schmitt almost 6 years ago

[TASK] Update SQL definition for sys_file_storage

There are missing indexes as well as a mismatch between the
TCA definition (and therefore actual usage) and the SQL
definition at database level.

This change
  • adds combined index to deleted / hidden to speedup the
    query initializing storages in Be_UserAuth
  • changes SQL definition for field "name" to match their
    usage in TCA

Resolves: #54923
Releases: 6.2
Change-Id: I86f345a941802304ed7fbe29d00ae38130f8e5d2
Reviewed-on: https://review.typo3.org/26768
Reviewed-by: Steffen Ritter
Tested-by: Steffen Ritter
Reviewed-by: Oliver Klee
Reviewed-by: Benjamin Mack
Tested-by: Benjamin Mack

History

#1 Updated by Gerrit Code Review almost 6 years ago

  • Status changed from New to Under Review

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/26768

#2 Updated by Gerrit Code Review almost 6 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/26768

#3 Updated by Gerrit Code Review almost 6 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/26768

#4 Updated by Gerrit Code Review almost 6 years ago

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/26778

#5 Updated by Gerrit Code Review almost 6 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/26768

#6 Updated by Gerrit Code Review almost 6 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/26768

#7 Updated by Gerrit Code Review almost 6 years ago

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

#8 Updated by Ingo Schmitt almost 6 years ago

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

#9 Updated by Riccardo De Contardi about 2 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF