Bug #92485

Missing index on fe_login felogin_forgotHash - long sql requests

Added by Tymoteusz Motylewski 18 days ago. Updated 11 days ago.

Status:
Resolved
Priority:
Should have
Category:
felogin
Target version:
-
Start date:
2020-10-05
Due date:
% Done:

100%

TYPO3 Version:
10
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

Due too missing index in the fe_login felogin_forgotHash column, on big databases > 100k feusers queries to forgot password takes a long time to process.

An index to fe_users should be added:

ALTER TABLE fe_users ADD INDEX felogin_forgot_hash (felogin_forgotHash);

Without the index on the big db the queries take several seconds:

UPDATE `fe_users` SET `password` = ?, `felogin_forgotHash` = ?, `tstamp` = ? WHERE `felogin_forgotHash` = ?
^^3895 ms

SELECT * FROM `fe_users` WHERE (`felogin_forgotHash` = ?) AND ((`fe_users`.`disable` = ?) AND (`fe_users`.`starttime` <= ?) AND ((`fe_users`.`endtime` = ?) OR (`fe_users`.`endtime` > ?))) LIMIT ?
^^2582 ms

SELECT COUNT FROM `fe_users` WHERE (`felogin_forgotHash` = ?) AND ((`fe_users`.`disable` = ?) AND (`fe_users`.`starttime` <= ?) AND ((`fe_users`.`endtime` = ?) OR (`fe_users`.`endtime` > ?)))
^^874 ms

Associated revisions

Revision 77988439 (diff)
Added by Tymoteusz Motylewski 11 days ago

[BUGFIX] Improve performance of the forgot password action

Add index on the felogin_forgotHash field of the fe_login table
to speed up db queries send when user requests password reminder.

Resolves: #92485
Releases: master, 10.4
Change-Id: I100557a82fe352eb6c078df65c6f8b81a1e92802
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/66055
Tested-by: TYPO3com <>
Tested-by: Simon Gilli <>
Tested-by: Christian Kuhn <>
Reviewed-by: Simon Gilli <>
Reviewed-by: Christian Kuhn <>

Revision 7e34b11b (diff)
Added by Tymoteusz Motylewski 11 days ago

[BUGFIX] Improve performance of the forgot password action

Add index on the felogin_forgotHash field of the fe_login table
to speed up db queries send when user requests password reminder.

Resolves: #92485
Releases: master, 10.4
Change-Id: I100557a82fe352eb6c078df65c6f8b81a1e92802
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/66115
Tested-by: TYPO3com <>
Tested-by: Christian Kuhn <>
Reviewed-by: Christian Kuhn <>

History

#1 Updated by Tymoteusz Motylewski 18 days ago

  • Description updated (diff)

#2 Updated by Georg Ringer 18 days ago

  • Status changed from New to Accepted

#3 Updated by Gerrit Code Review 17 days ago

  • Status changed from Accepted 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/c/Packages/TYPO3.CMS/+/66055

#4 Updated by Gerrit Code Review 11 days ago

Patch set 1 for branch 10.4 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/66115

#5 Updated by Tymoteusz Motylewski 11 days ago

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

Also available in: Atom PDF