Bug #54929

Speedup Category Listing by SQL Keys

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

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

100%

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

Description

When a category listing the the Backend is made, the following SQL Statements are issued:

SELECT sys_category.uid,sys_category.title,sys_category.t3ver_id,sys_category.t3ver_state,sys_category.t3ver_wsid,sys_category.t3ver_count,sys_category.hidden,sys_category.starttime,sys_category.endtime FROM sys_category WHERE sys_category.pid<>-1 AND sys_category.deleted=0 AND sys_category.sys_language_uid IN (-1, 0) ORDER BY sys_category.sorting ASC;

(Twice per Category Tree)

plus per each category element

SELECT uid FROM sys_category WHERE FIND_IN_SET('9',parent);

Possible Solution: Add two additional indexes to sys_category

Associated revisions

Revision 1044c801 (diff)
Added by Ingo Schmitt about 6 years ago

[TASK] Add index to sys_category

All page, content and file-records TCEforms render a category
tree in order to assign these. The tree building executes the
same queries quite often. In order to optimize these queries
this change adds
  • an index "category_parent" to sys_category.parent in order
    to for the parent category while index "parent" currently
    indexes the pid the record is stored.
  • a combined index "category_list" to columns pid, deleted
    and sys_language_uid.

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

History

#1 Updated by Gerrit Code Review about 6 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/26775

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

#3 Updated by Ingo Schmitt about 6 years ago

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

#4 Updated by Riccardo De Contardi over 2 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF