The Ultimate Guide to High Availability with MariaDB

Charset Narrowing optimization

You are viewing an old version of this article. View the current version here.

Charset Narrowing is an optimization that enables the optimizer to use comparisons like utf8mb3_key_column=utf8mb4_expression to construct ref access to utf8mb3_key_column. It is supported for comparisons of indexed column that use utf8mb3_general_ci to expressions that use utf8mb4_general_ci .

The optimization was introduced in MariaDB 10.6.16, MariaDB 10.10.7, MariaDB 10.11.6, MariaDB 11.0.4, MariaDB 11.1.3, MariaDB 11.2.2 where it is OFF by default, and in MariaDB 11.3.1, where it is ON by default.

Details

MariaDB supports both utf8mb3 and utf8mb4 character sets. It is possible to construct join queries that compare columns that use different collations.

Suppose, we have table users that uses utf8mb4:

create table users (
  user_name_mb4 varchar(100) collate utf8mb4_general_ci,
  ...
);

and table orders that uses utf8mb3:

create table orders (
  user_name_mb3 varchar(100) collate utf8mb3_general_ci,
  ...,
  INDEX idx(user_name_mb3)
);

One can join users to orders on user_name:

select * from orders, users where orders.user_name_mb3=users.user_name_mb4;

but without Charset Narrowing the optimizer won't be able to use the INDEX idx(user_name_mb3).

Controlling the optimization

The optimization is controlled by an optimizer_switch flag. Specify

set optimizer_switch='cset_narrowing=on';

to enable the optimization.

References

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.