Actions
Task #103779
open
Reduce size of hash in database for ReferenceIndex sys_refindex
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¶
hashis generated as a 32 char long hex in string format e.g.0123456789abcdef0123456789abcdefhashis stored as 32 char long utf8 stringhashis thePRIAMRY KEYand 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 forBTREEis 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
- C. convert from hex string to base64 string and store as 22 char long utf8 string: 16^32 < 64^22
Updated by Ayke Halder 7 months ago
- Related to Task #103748: Enrich ReferenceIndex with more relation data added
Updated by Ayke Halder 7 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%)
Updated by Gerrit Code Review 7 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