Project

General

Profile

Actions
Copy link

Task #101707

closed

Add index for column "channel" in table sys_log

Added by Ingo Fabbri over 1 year ago. Updated 10 months ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Logging
Target version:
-
Start date:
2023-08-17
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
12
PHP Version:
Tags:
Complexity:
no-brainer
Sprint Focus:

Description

This is a pretty time-consuming query when there are a lot of rows in sys_log - and no index present.

SELECT DISTINCT `channel` FROM `sys_log` ORDER BY `channel` ASC

see https://github.com/TYPO3/typo3/blob/v12.4.5/typo3/sysext/belog/Classes/Domain/Repository/LogEntryRepository.php#L188

EXPLAIN SELECT DISTINCT `channel` FROM `sys_log` ORDER BY `channel` ASC;
sys_log
---
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
---: --- --- --- --- --- --- --- --- ---: ---: ---
1 SIMPLE sys_log \N ALL \N \N \N \N 15126595 100.00 Using temporary; Using filesort

Same applies to this query:

SELECT DISTINCT `level` FROM `sys_log`;

see https://github.com/TYPO3/typo3/blob/v12.4.5/typo3/sysext/belog/Classes/Domain/Repository/LogEntryRepository.php#L215

I am not reviewing the PHP code here.

Just adding the proper index for each query already helps to reduce query execution-time from up to 200seconds down to some milliseconds.

Actions
Copy link

Also available in: Atom PDF