Review main Merged

61403[TASK] Add index to slug field

TYPO3com CR +2 V +2 Easy +2 −1 updated 12/27/19, 22:25

Commit message

`TYPO3\CMS\Core\Routing\PageRouter::getPagesFromDatabaseForCandidates`
looks-up candidates for the page using the `slug` field (and also sorts by
this value). This has some serious impact on the website performance on
systems with lots of pages. In order to optimize this, an index should be
set.

Review

  • Code review CR +2 Done
    • Benni Mack +2
    • Jonas Eberle +1
    • Oliver Hader +1
  • Verified V +2 Done
    • Benni Mack +2
    • Jonas Eberle +1
    • Oliver Hader +1
    • TYPO3com +1 CI

Issues 1

Files 1

Review log 3238

Show 1013 earlier messages
  1. TYPO3com uploaded patch set 1 08/02/19, 14:27

  2. Guido Schmechel commented on patch set 1 08/02/19, 14:29

    The slugField can be relatively long. Should we limit the index here? For example, to 10 characters?
  3. TYPO3com voted on patch set 1 V -1 08/02/19, 14:38 Completed build in 10m 39s on Fri, 2 Aug, 02:38 PM Test Summary: 16 of 47534 failed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-35472

  4. Josef Glatz updated the commit message in patch set 2 08/02/19, 14:44

  5. Josef Glatz commented on patch set 2 08/02/19, 14:45

    In terms of content, unfortunately, I can not contribute anything there. But if this will be merged, please fix it also for 9.5
  6. Josef Glatz wrote 08/02/19, 14:45

    Removed reviewer Josef Glatz.
  7. TYPO3com voted on patch set 2 V +1 08/02/19, 14:55 Completed build in 10m 44s on Fri, 2 Aug, 02:55 PM Test Summary: 47175 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-35475

  8. Jonas Eberle commented on patch set 2 V -1 08/05/19, 09:37

    I agree on the max. index length. If none is given on this 2048 VARCHAR, it might lead to problems with InnoDB (max. 767 bytes) any way. With the current patch I get "[42000][1071] Specified key was too long; max key length is 3072 bytes" (MariaDb 10.2, InnoDb, Dynamic row format) But as our slug data in big sites would be very similar in the first few chars, I would go for at least 32 characters. The possible maximum AFAIK would be 767/4 for mb4.
  9. Tymoteusz Motylewski commented on patch set 2 08/06/19, 07:31

    for the slug field we could use different collation (not mb4) as AFAIK no utf8mb4 characters should be allowed there.
  10. Jonas Eberle commented on patch set 2 08/06/19, 07:39

    Good idea. Would that be portable across MySQL/MariaDB/PostgreSQL/MSSQL? I have no experience with that.
  11. Tymoteusz Motylewski commented on patch set 2 10/10/19, 07:00

    Seems I'm wrong and we allow utf-8 characters in slugs. See https://review.typo3.org/c/Packages/TYPO3.CMS/+/61655
  12. Patrick Schriner commented on patch set 2 10/25/19, 08:45

    The query fetching slugs doesn't only query the "slug" field. Including "deleted" or "sys_language_uid" might speed this up even more? Regarding length: That's the reason why realurl used an md5 hashed field to speed up lookup queries.
  13. anonymized-52878 commented on patch set 2 V -1 11/01/19, 14:27

    Confirm that the patch is not working, giving following Error-Message: Specified key was too long; max key length is 3072 bytes" (MariaDb 10.2)
  1. Jonas Eberle uploaded patch set 3 11/25/19, 19:12

  2. TYPO3com voted on patch set 3 V +1 11/25/19, 19:19 Completed build in 07m 00s on Mon, 25 Nov, 07:19 PM Test Summary: 48160 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-38827

  3. anonymized-52878 commented on patch set 3 CR +1 11/26/19, 07:28

    Can confirm that it works with MariaDB 10.2. Bo Errors. Could not evaluate any performance improvements on the default page load process.
  4. Jörg Bösche commented on patch set 3 V +1 CR +1 12/01/19, 19:11

    No errors on MariaDB 10.4 and type InnoDB
  5. Oli Bartsch voted on patch set 3 CR +1 12/02/19, 06:26

  6. Oli Bartsch voted on patch set 3 V +1 12/03/19, 08:19

  7. Andreas Kienast commented on patch set 3 CR -1 12/03/19, 08:55

    Features need a ReST file and must not get backported to 9.5.
  8. Oli Bartsch commented on patch set 3 12/03/19, 11:08

    > Patch Set 3: Code-Review-1 > > Features need a ReST file and must not get backported to 9.5. For me this is anyhow more a bugfix than a feature. At least what the commit message reads. Maybe would be also fine for me making it a task according to the issue type.
  9. Jonas Eberle commented on patch set 3 12/03/19, 11:14

    I am not too much into the definitions. I'd say it is neither a bugfix (as "performance" might not qualify as bug) nor really a feature. To me TASK seems just right.
  10. Jonas Eberle uploaded patch set 4 12/03/19, 12:25

  11. TYPO3com voted on patch set 4 V +1 12/03/19, 12:37 Completed build in 10m 59s on Tue, 3 Dec, 12:37 PM Test Summary: 48367 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-39072

  12. Oliver Klee voted on patch set 4 CR +1 12/03/19, 12:43

  13. Oli Bartsch commented on patch set 4 12/03/19, 15:25

    > Patch Set 3: > > I am not too much into the definitions. I'd say it is neither a bugfix (as "performance" might not qualify as bug) nor really a feature. To me TASK seems just right. All right, as I said I'm also fine with TASK. Is there a reason why you removed 9.5? I think performance optimizations should also be backported as long as maintenance releases are possible.
  14. Jonas Eberle commented on patch set 4 12/03/19, 17:15

    > Is there a reason why you removed 9.5? I think performance optimizations should also be backported as long as maintenance releases are possible. I was not sure any more if that could go into 9.5 and just wanted to avoid further controversy.
  15. Oli Bartsch commented on patch set 4 12/04/19, 07:01

    > Patch Set 4: > > > Is there a reason why you removed 9.5? I think performance optimizations should also be backported as long as maintenance releases are possible. > > I was not sure any more if that could go into 9.5 and just wanted to avoid further controversy. Oke, anyway I would really like to see this also in 9.5. Maybe I should add a separate patch for 9.5 so we can safely merge this one and discuss the backport in the other patch independently. What do you think?
  16. Jonas Eberle commented on patch set 4 12/04/19, 07:52

    please go ahead
  17. Oli Bartsch commented on patch set 4 V +1 CR +1 12/04/19, 10:09

    > Patch Set 4: > > please go ahead All right, uploaded the patch for 9.5: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62532
  18. Guido Schmechel voted on patch set 4 CR +1 12/05/19, 10:50

  19. Oliver Hader commented on patch set 5 12/27/19, 21:27

    Published edit on patch set 4.
  20. Oliver Hader voted on patch set 5 V +1 CR +1 12/27/19, 21:27

  21. Oliver Hader commented on patch set 5 12/27/19, 21:28

  22. Jonas Eberle voted on patch set 5 V +1 CR +1 12/27/19, 21:53

  23. TYPO3com voted on patch set 5 V +1 12/27/19, 22:18 Completed build in 48m 08s on Fri, 27 Dec, 10:18 PM Test Summary: 69128 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-39623

  24. Benni Mack voted on patch set 5 V +2 CR +2 12/27/19, 22:25

  25. Benni Mack merged patch set 6 12/27/19, 22:25