Slow backend for systems/users with high amounts of sys_log entries
If a system/user has a lot of sys_log entries associated, the backend for that user becomes unbearably slow (20s+ for 250k entries). The reason behind this is a query in typo3/sysext/core/Classes/DataHandling/DataHandler.php:
6.2.9 line 7313: $res_log = $GLOBALS['TYPO3_DB']->exec_SELECTquery('*', 'sys_log', 'type=1 AND userid=' . (int)$this->BE_USER->user['uid'] . ' AND tstamp=' . (int)$GLOBALS['EXEC_TIME'] . ' AND error<>0');
Because there is no index over error, the query cannot be optimized properly. When adding an index over "error", the speed is is improved dramatically (20s => 1s) and if I add an index over type, userid, tstamp and error, the query obviously is sped up even more dramatically (<0.01s).
ALTER TABLE `sys_log` ADD INDEX `print_error_log` (`type`, `userid`, `tstamp`, `error`);
#1 Updated by Morton Jonuschat over 3 years ago
- Category changed from Backend API to Performance
- Status changed from New to Needs Feedback
- Assignee set to Morton Jonuschat
Could you check if
ALTER TABLE `sys_log` ADD INDEX `print_error_log` (`userid`, `type`, `tstamp`);
helps to speeds up your query and if so, by how much?
This index would be a bit less specific but could be used by more queries in the backend.