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
Status: | Resolved | Start date: | 2014-01-03 | |
---|---|---|---|---|
Priority: | Should have | Due date: | ||
Assigned To: | Ingo Schmitt | % Done: | 100% |
|
Category: | Performance | Spent time: | - | |
Target version: | 6.2.0 | |||
TYPO3 Version: | 6.2 | Complexity: | ||
PHP Version: | 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
Associated revisions
[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 1 year 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 1 year ago
After Applying 54725 and 54726 my speed improvement by 100 cycles in AB was 10%
#3 Updated by Gerrit Code Review over 1 year 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 1 year 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 1 year 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 1 year 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 1 year ago
- Parent task set to #51094
#8 Updated by Gerrit Code Review over 1 year 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 1 year ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 6335a24af49709e956c1ceb177f999a8fbcc6058.
#10 Updated by Steffen Ritter over 1 year ago
- % Done changed from 100 to 90
#11 Updated by Steffen Ritter over 1 year ago
- Assigned To set to Ingo Schmitt
#12 Updated by Steffen Ritter over 1 year ago
- % Done changed from 90 to 100