Project

General

Profile

Actions

Task #103779

open

Reduce size of hash in database for ReferenceIndex sys_refindex

Added by Ayke Halder about 2 months ago. Updated about 2 months ago.

Status:
Under Review
Priority:
Should have
Assignee:
-
Category:
-
Target version:
-
Start date:
2024-05-02
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
13
PHP Version:
Tags:
Complexity:
Sprint Focus:

Description

The ReferenceIndex table sys_refindex is heavily used to keep track of relations between all kind of rows from multiple tables.
The table sys_refindex stores thousands of rows to do its job.

It would be beneficial to keep sys_refindex rows and its indexes as small as possible.

hash column

  • hash is generated as a 32 char long hex in string format e.g. 0123456789abcdef0123456789abcdef
  • hash is stored as 32 char long utf8 string
  • hash is the PRIAMRY KEY and becomes part of every index. (3 indexes in total right now).

Reduce size of hash

  • A. store as binary: not feasable as tests cases are hard to implement and performance for BTREE is not the best.
  • B. store with charset latin1: ext_tables.sql is not able to deal with charset restrictions on fieldlevel right now.

See dicussion on slack:
https://typo3.slack.com/archives/C03AM9R17/p1714293821710339

New approach:
  • C. convert from hex string to base64 string and store as 22 char long utf8 string: 16^32 < 64^22

Related issues 1 (0 open1 closed)

Related to TYPO3 Core - Task #103748: Enrich ReferenceIndex with more relation dataResolved2024-04-27

Actions
Actions #1

Updated by Ayke Halder about 2 months ago

  • Related to Task #103748: Enrich ReferenceIndex with more relation data added
Actions #2

Updated by Ayke Halder about 2 months ago ยท Edited

size comparison

OPTIMIZE TABLE `sys_refindex`;
SHOW TABLE STATUS FROM `db` WHERE `Name` = 'sys_refindex';

hash as 32 char hex

  • Rows: 96465
  • Data_length: 14221312 (+8.1%)
  • Index_length: 21626880 (+17.1%)

hash as 22 char Base64-encoded binary

  • Rows: 96465
  • Data_length: 13156352 (-7.5%)
  • Index_length: 18464768 (-14.6%)
Actions #3

Updated by Gerrit Code Review about 2 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/+/84111

Actions

Also available in: Atom PDF