Review main Abandoned

84111[TASK] Reduce size of ReferenceIndex

Ayke Halder CR 0 V 0 Normal +52 −3 updated 04/07/25, 13:33

Commit message

For ReferenceIndex the hash column is stored as Base64 instead of hex,
thereby reducing the size from 32 chars to 22 chars.

This also introduces two general functions for converting to Base64:
* GeneralUtility::binToBase64()
* GeneralUtility::hexToBase64()

Review

  • Code review CR 0
  • Verified V 0

Issues 2

Files 3

Review log 56

  1. Ayke Halder uploaded patch set 1 05/02/24, 16:15

  2. core-ci voted on patch set 1 V -1 05/02/24, 16:21 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/57104

  3. Christian Kuhn commented on patch set 1 04/07/25, 09:14

    hey. we will for now go with https://review.typo3.org/c/Packages/TYPO3.CMS/+/87757 : This does not solve all potential performance issues with large sys_refindex tables, but the indexes become much smaller already. mid-term, we should probably switch to a 64bit int autoincrement in favor of the hash field, which would have further advantages, but that is much more effort to implement. i'll abandon here for now since the index length shortening is a good solution for now.
  4. Christian Kuhn abandoned the change 04/07/25, 09:14

  5. Ayke Halder commented on patch set 1 04/07/25, 12:46

    Reply

    @lolli@schwarzbu.ch I could not reproduce any improvement of db size when `hash` columns character-set is changed to `ascii_bin` instead of default `utf8mb4_unicode_ci`. I did a before/after comparison with these statistics: <pre><code class="sql"> OPTIMIZE TABLE `sys_refindex`; SHOW TABLE STATUS FROM `db` WHERE `Name` = 'sys_refindex'; </code></pre> Maybe these statistics do not correctly calculate the used space, do they?
  6. Christian Kuhn commented on patch set 1 04/07/25, 13:33

    Reply

    compare something like this before/after: select database_name, index_name, (1.0*stat_value*@@innodb_page_size/1024/1024) as index_size, table_name from mysql.innodb_index_stats where stat_name = 'size' and index_name not in ('GEN_CLUST_INDEX') and database_name = 'coremaster' and table_name='sys_refindex' order by index_size desc;