The Charset Narrowing optimization handles equality comparisons like:
It enables the optimizer to construct ref access to utf8mb3_key_column based on this equality. The optimization supports comparisons of columns that use utf8mb3_general_ci to expressions that use utf8mb4_general_ci .
The optimization was introduced in MariaDB 10.6.16, , MariaDB 10.11.6, , and , where it is OFF by default. From , it is ON by default.
MariaDB supports both the UTF8MB3 and UTF8MB4 . It is possible to construct join queries that compare values in UTF8MB3 to UTF8MB4.
Suppose, we have the table 'users that uses UTF8MB4:
and table orders that uses UTF8MB3:
One can join users to orders on user_name:
Internally the optimizer will handle the equality by converting the UTF8MB3 value into UTF8MB4 and then doing the comparison. One can see the call to CONVERT in EXPLAIN FORMAT=JSON output or Optimizer Trace:
This produces the expected result but the query optimizer is not able to use the index over orders.user_name_mb3 to find matches for values of users.user_name_mb4.
The EXPLAIN of the above query looks like this:
The Charset Narrowing optimization enables the optimizer to perform the comparison between UTF8MB3 and UTF8MB4 values by "narrowing" the value in UTF8MB4 to UTF8MB3. The CONVERT call is no longer needed, and the optimizer is able to use the equality to construct ref access:
The optimization is controlled by an flag. Specify:
to enable the optimization.
: utf8mb3_key_col=utf8mb4_value cannot be used for ref access
Blog post:
This page is licensed: CC BY-SA / Gnu FDL
utf8mb3_key_column=utf8mb4_expressionCREATE TABLE users (
user_name_mb4 VARCHAR(100) COLLATE utf8mb4_general_ci,
...
);CREATE TABLE orders (
user_name_mb3 VARCHAR(100) COLLATE utf8mb3_general_ci,
...,
INDEX idx1(user_name_mb3)
);SELECT * FROM orders, users WHERE orders.user_name_mb3=users.user_name_mb4;CONVERT(orders.user_name_mb3 USING utf8mb4) = users.user_name_mb4EXPLAIN SELECT * FROM orders, users WHERE orders.user_name_mb3=users.user_name_mb4;
+------+-------------+--------+------+---------------+------+---------+------+-------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+-------+-------------------------------------------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 10330 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+--------+------+---------------+------+---------+------+-------+-------------------------------------------------+SET optimizer_switch='cset_narrowing=ON';
EXPLAIN SELECT * FROM orders, users WHERE orders.user_name_mb3=users.user_name_mb4;
+------+-------------+--------+------+---------------+------+---------+---------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+---------------------+------+-----------------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
| 1 | SIMPLE | orders | ref | idx1 | idx1 | 303 | users.user_name_mb4 | 1 | Using index condition |
+------+-------------+--------+------+---------------+------+---------+---------------------+------+-----------------------+SET optimizer_switch='cset_narrowing=ON';