2

What is the role of the field crc_string in the translation table?
I know what crc is in general, but how is this field used to detect (and maybe correct) errors in the strings, if this is its real use at all?

1 Answer 1

1

I think the main purpose of that column is to make an unique index more useful. Here is just one place where it is used (as I seen on my 2.4.2 EE):

<constraint xsi:type="unique" referenceId="TRANSLATION_STORE_ID_LOCALE_CRC_STRING_STRING">
    <column name="store_id"/>
    <column name="locale"/>
    <column name="crc_string"/>
    <column name="string"/>
</constraint>

I think btree index works this way: find record in index by store_id -> find inside record using locale -> find inside record using small crc_string -> and, finally, find inside that node long string (usually 1 record, but could be 2 or 3, based on table size).

That way we can find record much faster then in case where we should search among long text records.

This is what this index looks like:

indexes of translation

Quotation from the MySQL docs:

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

And about multi-column indexes:

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

PS: This is my guess, I am not very knowledgeable about how databases work

2
  • Thanks for the answer, but I don't think that's it. The index you mentioned is just a unique index. there is no btree index on the crc_string field. Also, I didn't find any code that would do a where crd_string = ?' (maybe I just missed it). If there was a where` statement somewhere then an index would have been justified.
    – Marius
    May 14, 2021 at 7:13
  • @Marius you can check the INDEXES using the SHOW INDEXES FROM translation command and you see that this composite index is BTREE index (I've attached screenshot to the answer) and the crc_string has position 3, before the string which on position 4. Don't know how actually the search in table works, but when you searching using a column which is part of the unique index mysql perhaps search using that index first. May 14, 2021 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.