Issue Task Closed

#101707Add index for column "channel" in table sys_log

Ingo Fabbri 08/17/23, 16:47 updated 01/30/24, 08:31

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.

Reviews 2