Review main Ready for review

81309[TASK] Improve page resolution performance

Markus Klein CR 0 V 0 Easy +1 −1 updated 11/14/26, 11:24

Commit message

PageSlugCandidateProvider employs a central
SQL query to fetch page candidates for a given slug.
The query uses four fields for result ordering,
where only the slug is part of an index.
Adding all 4 fields to the index speeds this up.

This patch adjust the existing index, as any existing
query, using the previous index, will still use
the new index.

Test this change

Run a command in your clone of the TYPO3 Core. Then try what the change does and vote on Verified in Gerrit. How to review a patch

Check out patch set 5
git fetch https://review.typo3.org/Packages/TYPO3.CMS refs/changes/09/81309/5 && git checkout FETCH_HEAD
Cherry-pick onto main
git fetch https://review.typo3.org/Packages/TYPO3.CMS refs/changes/09/81309/5 && git cherry-pick FETCH_HEAD

Review

  • Code review CR 0 Needs a vote
  • Verified V 0 Needs a vote
    • core-ci +1 CI

Merge conflicts 5

Issues 1

Files 1

Review log 2934

Show 89 earlier messages
  1. Markus Klein uploaded patch set 1 10/02/23, 10:51

  2. Dmitry Dulepov commented on patch set 1 V +1 CR +1 10/02/23, 10:57

    Tested with this index in a real environment.
  3. core-ci voted on patch set 1 V +1 10/02/23, 11:04 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47348

  4. Georg Ringer voted on patch set 1 V +1 CR +1 10/02/23, 11:06

  5. Stefan Bürk voted on patch set 1 CR -1 10/02/23, 11:21

  6. Stefan Bürk commented on patch set 1 CR -2 10/02/23, 11:28

    temp -2, will have a deeper look into it later. Not as a blocker, just to be sure. Indexes are a fragile thing, specially accross database vendors. There are a lot of things to consider, and I want to look deeper into it. Additionally, what we should consider is that this change introduces database structure changes on patch-level upgrades. At least, we need to ensure that this is stated in the release statement after this have been mergend. Therefore, beside technical consideration I would say we need at least a look of Benni/Oliver before merging it (if it is okay) so they are aware for the next release of this case.
  7. Markus Klein commented on patch set 1 10/02/23, 13:41

    Reply

    Thanks for taking care. Regarding the change in bugfix-releases: Since this change is not mandatory, a DB change is okay and we did this many times in the past already.
  8. Oliver Hader commented on patch set 1 10/04/23, 08:37

    How to verify, that it enhances performance? What scenario (amount of pages) is required and how was it tested? I could not verify locally (with an introduction site) that there is a significant change in processing a request.
  9. Dmitry Dulepov commented on patch set 1 10/04/23, 08:42

    @Oliver Have a look to the ticket and 'explain' statements there. It was tested in production.
  1. Oliver Hader commented on patch set 1 10/04/23, 08:43

    Reply

    From my POV it would be fine to back-port this to v12, since altering the `KEY` is optional and not considered a breaking change.
  2. Oliver Hader commented on patch set 1 10/04/23, 08:46

    Reply

    Thanks, I already did. However, I was looking for numbers, timing or something like "improved by 20%". And that surely depends on the scenario (amount of pages, avg. length of slugs/depth).
  3. Dmitry Dulepov commented on patch set 1 10/04/23, 08:58

    I cannot give numbers (did not track them) but this function is called twice per request and it seems that results are not cached by mysql. The site has about 4000 pages, depth is about 5 levels. Typically it considers 2x slugs per level (so 4 on level 2). When they are selected, they have to be sorted by several fields, which makes it slow. I did two optimisations there: indexes, which you see here + added an xclass with a cache for the result to prevent second query and calculations. This place stopped being a bottleneck for the site. The site was upgraded from 9.5 to 12.4 and has major issues with performance. We used to run easily on 2 cpus and now it needs 6 and overloads. So I am looking what makes it slow. There is a similar issue with sys_category table, there select happens by slug and some fields and considers around 300 records. I made it down to only 2 with proper indexes. So this is not a proposal out of the blue, it is a work that goes for several days now and I want to share its results, so everybody can have improvements.
  4. Markus Klein commented on patch set 1 10/04/23, 11:01

    Reply

    Since "filesort" is in the game, I would say it heavily depends on your I/O capabilities how much things improve. But the slower your I/O, the larger the improvement should be. At least to my understanding.
  5. Oliver Hader commented on patch set 1 10/04/23, 14:23

    Reply

    I've just added a comment to the Forge ticket https://forge.typo3.org/issues/102076#note-2 tl;dr: Adding an index won't help to get rid of `filesort`. The example in the Forge report was using a static `slug IN ('')` query - that was basically the reason the `filesort` disappeared, it was not the index.
  6. Markus Klein commented on patch set 1 10/04/23, 15:22

    Reply

    @Olly: I don't understand your reasoning here fully. The example in forge uses the same query for before and after the index is added. (both with `slug IN ('')`) So for me it makes sense that the index makes the filesort go away for the `order by` part. But it is indeed interesting that filesort is added back, once you employ more values for the slug condition.
  7. Oliver Hader commented on patch set 1 10/04/23, 20:51

    Reply

    Let's imagine a simplified query like `SELECT uid FROM pages WHERE slug IN ('static') ORDER BY slug, uid;`. In this case, all potential records would have the same value `'static'` for the `slug` field - it is not required to apply a sorting for `slug`, and MySQL directly optimizes that, actually even without an index. Maybe MariaDB behaves totally different here - I did only test with MySQL 8.0.34.
  8. Markus Klein commented on patch set 1 10/05/23, 06:32

    Reply

    Roger. I tested stuff too, see ticket. On MariaDB, it seems very similar.
  9. Oliver Klee voted on patch set 1 CR +1 10/06/23, 20:03

  10. Markus Klein commented on patch set 1 04/02/24, 15:51

    How do we proceed here?
  11. Stefan Bürk voted on patch set 1 CR 0 04/02/24, 22:47

  12. Markus Klein abandoned the change 09/10/24, 18:23

    No conclusion so far
  13. Markus Klein restored the change 09/08/26, 08:15

  14. Markus Klein updated the commit message in patch set 2 CR +1 V +1 09/08/26, 08:16

  15. Markus Klein uploaded patch set 3 09/08/26, 08:18

  16. Markus Klein commented on patch set 3 09/08/26, 08:18

    Restored, based on findings documented in the ticket
  17. core-ci voted on patch set 3 V +1 09/08/26, 08:25 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/108064

  18. Oliver Klee voted on patch set 3 CR +1 09/08/26, 09:45

  19. core-ci voted on patch set 2 V -1 09/08/26, 12:00 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/108063

  20. Markus Klein commented on patch set 3 09/08/26, 15:39

    Reply

    Done

    Reply

    Done

    Reply

    Done
  21. Markus Klein commented on patch set 3 11/11/26, 15:19

    Reply

    AI conclusion in https://forge.typo3.org/issues/102076#note-13 says: go for it
  22. Benni Mack updated the commit message in patch set 4 CR +1 V +1 11/14/26, 07:50

  23. core-ci voted on patch set 4 V -1 11/14/26, 08:01 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/114420

  24. Benni Mack rebased onto patch set 5 V -1 11/14/26, 11:07

  25. core-ci voted on patch set 5 V +1 11/14/26, 11:24 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/114442