2

I have a table with ~100mln rows. One of the columns is a VARCHAR(64) with utf8mb4 encoding for storing user nicknames (others are few integers).

The data in that column has MAX(LENGTH()) == 42 and MAX(CHAR_LENGTH()) == 24 (I can attach an image of distribution of lengths if needed). ~90% of rows only use ASCII characters.

From the MySQL docs,

CHAR(30) can hold up to 30 characters. [...] a CHAR(255) column can exceed 768 bytes

So does it mean it can store N characters, even multibyte? How about a varchar, does the specified size mean bytes or characters?

My table is mostly write-intensive (INSERT O.D.K. UPDATE). Is it a good idea to switch to constant-length field (especially with such not-very-long strings) versus having them as a varying-length field, probably stored somewhere else on the disk?

Byte length distribution:

Byte length distribution

Char_length distribution:

Char_length distribution

7
  • What version of MySQL are you running? (I need this before jumping on max lengths.)
    – Rick James
    Sep 20, 2023 at 23:04
  • A utf8mb4 character takes between 1 and 4 bytes. The 768 must be referring to utf8, not utf8mb4. CHAR and VARCHAR refer to characters.
    – Rick James
    Sep 20, 2023 at 23:05
  • "switch to constant-length field" -- NO. (Except if they are truly fixed length, such as country_code, zipcode, etc.)
    – Rick James
    Sep 20, 2023 at 23:07
  • @RickJames Ver 8.0.34 Yes, it was written that for an encoding that uses more than 3 bytes per character (like mb4) the CHAR(255) can exceed 3*256 bytes. Obviously. So now the only question is whether it is worth to reduce the length of my varchar column (or maybe switch to varbinary?). I've added the distribution plots to the post.
    – herhor67
    Sep 21, 2023 at 9:26
  • Looks like the average byte (or character) length of your strings is around 13. So, a VARCHAR(..) would consume, on average, 2+13 bytes. While `CHAR(20) would always take 20 bytes. Less space is better.
    – Rick James
    Sep 21, 2023 at 17:35

1 Answer 1

0

So does it mean it can store N characters, even multibyte? How about a varchar, does the specified size mean bytes or characters?

Yes, the length is measured in characters, not bytes. See also the docs for BINARY/VARBINARY data types

Is it a good idea to switch to constant-length field (especially with such not-very-long strings) versus having them as a varying-length field, probably stored somewhere else on the disk?

No. Constant length fields will always occupy the maximum possible space, while varchars will usually occupy much less space. Less space means more rows per disk page, thus less page reads/writes and less memory needed.

Also see this question for CHAR/VARCHAR comparison

8
  • CHAR is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to fragmentation. That was one of the reasons I asked about moving to constant-length - almost all rows are updated at least daily (though I know that it may not be much compared to abilities of DBMS)
    – herhor67
    Sep 19, 2023 at 14:28
  • @herhor67 it would matter if that field (the nickname) changed frequently, but I suppose that is not the column which is updated more.
    – Andrea B.
    Sep 19, 2023 at 15:02
  • All fields are updated from the external API. Yes, they usually don't "change", but they are still in the insert query.
    – herhor67
    Sep 19, 2023 at 19:43
  • @herhor67 if the new value has the same length of the old value, the row has the same length and there is no fragmentation
    – Andrea B.
    Sep 19, 2023 at 20:02
  • 1
    @AndreaB. - InnoDB will temporarily hold the old and new copy of the row during UPDATE' this threatens fragmentation more than fixed/dynamic length.
    – Rick James
    Sep 20, 2023 at 23:10

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.