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.
[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
Reviewed-by: Anja Leichsenring
Tested-by: Anja Leichsenring
Reviewed-by: Marcin Sągol
Reviewed-by: Benjamin Mack
Tested-by: Benjamin Mack
#7 Updated by Christian Kuhn almost 6 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) ...