0

I have the following table:

CREATE TABLE `tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s_id` int(10) unsigned NOT NULL,
  `a_token` char(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `a_token_exp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_a_token` (`a_token`) USING HASH,
  KEY `f_seid` (`s_id`),
  CONSTRAINT `f_seid` FOREIGN KEY (`s_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

and I want to change the charset for a_token since we use only ascii characters using this command:

ALTER TABLE tokens MODIFY a_token CHAR(40) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL;

and the new SHOW CREATE TABLE shows:

CREATE TABLE `tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id` int(10) unsigned NOT NULL,
  `a_token` char(40) CHARACTER SET ascii NOT NULL,
  `a_token_exp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_a_token` (`a_token`) USING HASH,
  KEY `f_seid` (`s_id`),
  CONSTRAINT `f_seid` FOREIGN KEY (`s_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

and then I run OPTIMIZE TABLE oauth_session_access_tokens.

This query should (I'm not sure) the size of the table for my schema:

SELECT table_name AS "Table",
      ((data_length + index_length)) AS "Size"
FROM information_schema.TABLES
WHERE table_schema = "test"
ORDER BY (data_length + index_length) DESC;

but the specific table tokens always returns 49152 before and after the ALTER TABLE.

Can I suppose that even with CHAR and utf8, if there are only ASCII chars, then the field doesn't preallocate the space for utf8?

0

2 Answers 2

2

Short answer: Yes, switch to ASCII.

Looooong answer: There are many aspects to this question.

  • If the strings actually vary in length, use VARCHAR instead of CHAR.
  • If the strings are fixed in length, use CHAR. (40 sounds like SHA1, which is fixed length).
  • If the string is always hex, use UNHEX() when storing and HEX() when fetching, then store into a BINARY or VARBINARY column of half the size (BINARY(20) for SHA1). That saves 20 bytes per row for Ascii.
  • For older versions of MySQL, and varying length, CHAR pads to the given length.
  • For older versions of MySQL, CHAR with utf8 would force 3 bytes per columns, wasting two of them for hex. This is possibly your main question.
  • If you leave it as CHAR or VARCHAR, consider what COLLATION to use. For hex, any collation ending with _ci is probably appropriate. (For Base64 text, _bin is appropriate.)
  • 49152 bytes = 3*16KB. This implies that you have so few rows in the table that 1 block is for data, and 1 block for each of the two secondary indexes. Even if the table has exactly 1 row, the table will occupy exactly 49152 bytes. Or 100 rows is likely to be that same size.
  • 49152 bytes is insignificant these days. Even with a million rows, the table's size is not likely to be significant. For a billion rows, yes, study my points here. (However, for a billion rows, another issue arises -- INT UNSIGNED overflows at about 4 billion.)
  • SHA1 (or any other 'random' string) will have performance problems when the index becomes bigger than RAM.
  • In the context of this question, Ascii and latin1 perform similarly.
  • (There are probably more things I could point out. Most will be minor.)
8
  • Hey Rick, thanks for this long answer. Followup question on point 5: where can I find for which version MySQL stops forcing 3 bytes? Because what I'm try to demonstrate is that, in terms of space (not performance), switching from utf8 to ascii doesn't gain anything. I tried the for a couple of hours without finding this information. At application level it's always 40 chars and always ASCII.
    – dierre
    Jun 14, 2021 at 22:15
  • I also suspect ROW_FORMAT is involved in this mystery by the way.
    – dierre
    Jun 14, 2021 at 22:31
  • @dierre - I added a bullet item, and a possible version number of the change. You might be able to test the size via many rows of CHAR(255), each with a single letter; compare utf8 vs. ascii. Also try with REPEAT('x', 255)`. At what point does Data_length go past 16KB.
    – Rick James
    Jun 15, 2021 at 3:51
  • I suspect you didn't save the comment, I can't see the new item. I will try to test the way you suggested.
    – dierre
    Jun 15, 2021 at 7:13
  • @dierre The tip I've seen in the manual about utf8 padding to 3 bytes per character only applies to CHAR, not VARCHAR, and only in version MySQL 5.1 and older. Jun 15, 2021 at 12:55
5

UTF-8 is a variable-length character encoding. For characters in the ASCII range, it only takes 1 byte per character. It only uses 2, 3, or 4 bytes per character for those characters that require it.

The wikipedia article on UTF-8 has a good explanation and an illustration of how the multi-byte encodings work. https://en.wikipedia.org/wiki/UTF-8

So there's little downside to using UTF-8 even if you only have ASCII characters to store.

1
  • Hi Bill, thanks, I was aware of how UTF-8 works, but unfortunately it depends on how MySQL actually implements it, which I was not sure about. As you can see from Rick's answer it seems depends on MySQL version.
    – dierre
    Jun 15, 2021 at 7:16

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.