Task #101707: Add index for column "channel" in table sys_log - TYPO3 Core - TYPO3 Forge

Actions

Copy link

Task #101707

closed

Add index for column "channel" in table sys_log

Added by Ingo Fabbri 9 months ago. Updated 4 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:

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

Updated by Gerrit Code Review 9 months ago

Status changed from New to Under Review

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

Actions

Copy link

Updated by Ingo Fabbri 9 months ago

After indexes added:

sys_log
---

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
---:	---	---	---	---	---	---	---	---	---:	---:	---
1	SIMPLE	sys_log	\N	range	channel	channel	82	\N	5	100.00	Using index for group-by

Actions

Copy link

Updated by Ingo Fabbri 9 months ago

Before:

SELECT DISTINCT `channel` FROM `sys_log` ORDER BY `channel` ASC;
/* Affected rows: 0  Found rows: 5  Warnings: 0  Duration for 1 query: 00:01:40.0 */

After:

SELECT DISTINCT `channel` FROM `sys_log` ORDER BY `channel` ASC;
/* Affected rows: 0  Found rows: 5  Warnings: 0  Duration for 1 query: 0.022 sec. */

Actions

Copy link

Updated by Ingo Fabbri 9 months ago

Comparison for `level` looks mostly the same.

Actions

Copy link

Updated by Gerrit Code Review 9 months ago

Patch set 1 for branch 12.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/+/80660

Actions

Copy link