Issue Bug Closed

#92485Missing index on fe_login felogin_forgotHash - long sql requests

Tymoteusz Motylewski 10/05/20, 22:01 updated 12/22/20, 11:04

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

Reviews 2