Bug #55180

Epic #55070: Workpackages

Epic #55065: WP: Overall System Performance (Backend and Frontend)

Task #55179: Optimize SQL Performance

Increase Determination of siteroot page

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

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

100%

Estimated time:
2.00 h
TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
easy
Is Regression:
No
Sprint Focus:

Description

TYPO3 detects the rootpage by issuing a where clause on the pages table:

Count: 786 Time=0.04s (35s) Lock=0.00s (0s) Rows=0.0 (0), webuser[webuser]@localhost
SELECT uid FROM pages WHERE deleted=N AND hidden=N AND is_siteroot=N LIMIT N

no index on is_siteroot and hidden.


Related issues

Related to TYPO3 Core - Bug #55534: Fix typo in typo3/sysext/core/ext_tables.sql Closed 2014-01-31
Related to TYPO3 Core - Bug #59824: Better index for determineSiteRoot Closed 2014-06-23

Associated revisions

Revision 7a71192e (diff)
Added by Ingo Schmitt over 5 years ago

[BUGFIX] Add index to table pages

To detects the siteroot by querying the pages table with the
columns deleted, hidden, is_siteroot. This patch introduces
a combined index on those colums

Resolves: #55180
Releases: 6.2
Change-Id: Icae63670419cf9bc0029e2aa570cdf0108b9d62a
Reviewed-on: https://review.typo3.org/27154
Reviewed-by: Anja Leichsenring
Tested-by: Anja Leichsenring
Reviewed-by: Marcin SÄ…gol
Reviewed-by: Benjamin Mack
Tested-by: Benjamin Mack

History

#1 Updated by Markus Klein almost 6 years ago

Hi Ingo.

Count: 786
Is that for one single FE/BE request??

#2 Updated by Ingo Schmitt over 5 years ago

  • Category set to Performance

Is for each FE Request

#3 Updated by Gerrit Code Review over 5 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/27154

#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/27154

#5 Updated by Ingo Schmitt over 5 years ago

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

#6 Updated by Ingo Schmitt over 5 years ago

  • Estimated time changed from 0.50 h to 2.00 h

#7 Updated by Christian Kuhn almost 5 years ago

Actually, this query is not fired this way at all!

the siteroot determination works by first finding the uid of the requested page, then getting its rootline, then iterating over it to see which of it has is_siteroot set.

where did this query actually came from, and which code part of the core fires it? i could not find anything like that, so imho the created index is bogus. if some extension (like TV) does stuff like that, it should add its index on its own.

... still investigating on this since i'm not 100% sure about those claims ... i woke up again on this topic because of pending patch https://review.typo3.org/#/c/31082/ which wants to change this index again (also for questionable reasons) ...

#8 Updated by Riccardo De Contardi almost 2 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF