4 ms·

Hi! Former Product Manager for MySQL Server here. I have a blog post on this here: https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ htt

by morgo 8y ago

Hi! Former Product Manager for MySQL Server here. I have a blog post on this here: https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov... The default for MySQL 8.0 (current GA) is utf8mb4. I also recommend reading this introduction post here: http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf...

im3w1l 8y ago
Do you have a collation were no two different strings compare equal? Have you considered imposing an arbitrary order where you don't know which one "should" go first?
- morgo 8y ago
  Yes - there is a binary collation. w.r.t. arbitrary: The ordering must be deterministic so that indexes can maintain sorted order. It's possible to create a custom collation to have your own ordering - but I would say most users don't do that.
  - im3w1l 8y ago
    Sorry if I was unclear I meant some ordering that sorts by natural language but breaks ties in some arbitrary (but deterministic) manner, e.g. by binary.
JdeBP 8y ago
> An optimization was chosen to limit utf8 to 3 bytes You chose not to define the UNICODE_32BIT macro that was in the original source from 2002? * https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b01bc4e4395de9e4dd2a1b0cf23#diff-95de3baa9e399c689954d1b8442d0be8R1611 https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b0...
- morgo 8y ago
  The optimization was made at a different time in history. As I wrote in another comment here: while the decision is regrettable, because of a desire for backwards compatibility and simplified upgrade there wasn't an easy way out of it. The utf8[mb4] support in MySQL 8.0 is much better than prior releases. The team worked really hard on eliminating the use cases for utf8mb3 (performance) so that "just use the default of utf8mb4" can work for everyone.
  - jeltz 8y ago
    Is it really an optimization? I always assumed they picked 3 bytes to make sure a varchar(255) still would fit within the InnoDB field size limit of 768 bytes, so they could avoid/postpone having to fix InnoDB to support variable size fields.
    - morgo 8y ago
      This decision was made before InnoDB was the default. While the performance differences are historical now, consider the case that because of fixed sized buffers, one query could switch from a temp table in memory to a temp table on disk. The profile of the query has effectively changed.
      - jeltz 8y ago
        Yes, but did it happen before InnoDB was widely used?
  - JdeBP 8y ago
    You haven't answered my question at all, which was whether this optimization (as you put it) was the choice not to define that macro.

by morgo 8y ago

im3w1l 8y ago

Do you have a collation were no two different strings compare equal? Have you considered imposing an arbitrary order where you don't know which one "should" go first?

morgo 8y ago
Yes - there is a binary collation. w.r.t. arbitrary: The ordering must be deterministic so that indexes can maintain sorted order. It's possible to create a custom collation to have your own ordering - but I would say most users don't do that.
- im3w1l 8y ago
  Sorry if I was unclear I meant some ordering that sorts by natural language but breaks ties in some arbitrary (but deterministic) manner, e.g. by binary.

JdeBP 8y ago

> An optimization was chosen to limit utf8 to 3 bytes You chose not to define the UNICODE_32BIT macro that was in the original source from 2002? * https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b01bc4e4395de9e4dd2a1b0cf23#diff-95de3baa9e399c689954d1b8442d0be8R1611 https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b0...

morgo 8y ago
The optimization was made at a different time in history. As I wrote in another comment here: while the decision is regrettable, because of a desire for backwards compatibility and simplified upgrade there wasn't an easy way out of it. The utf8[mb4] support in MySQL 8.0 is much better than prior releases. The team worked really hard on eliminating the use cases for utf8mb3 (performance) so that "just use the default of utf8mb4" can work for everyone.
- jeltz 8y ago
  Is it really an optimization? I always assumed they picked 3 bytes to make sure a varchar(255) still would fit within the InnoDB field size limit of 768 bytes, so they could avoid/postpone having to fix InnoDB to support variable size fields.
  - morgo 8y ago
    This decision was made before InnoDB was the default. While the performance differences are historical now, consider the case that because of fixed sized buffers, one query could switch from a temp table in memory to a temp table on disk. The profile of the query has effectively changed.
    - jeltz 8y ago
      Yes, but did it happen before InnoDB was widely used?
- JdeBP 8y ago
  You haven't answered my question at all, which was whether this optimization (as you put it) was the choice not to define that macro.