Sargable UPPER

Starting from MariaDB 11.3, expressions in form

UPPER(key_col) = expr
UPPER(key_col) IN (constant-list)

are sargable if key_col uses collation utf8mb3_general_ci or utf8mb4_general_ci.

Sargable means the optimizer is able to use such conditions to construct access methods and also estimate their selectivity, or perform partition pruning.

Example

create table t1 (
  key1 varchar(32) collate utf8mb4_general_ci,
  ...
  key(key1)
);

MariaDB [test]>  explain select * from t1 where UPPER(key1)='ABC'
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | t1    | ref  | key1          | key1 | 131     | const | 1    | Using where; Using index |
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

Note that ref access is used.

An example with join:

MariaDB [test]>  explain select * from t0,t1 where upper(t1.key1)=t0.col;
+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
|    1 | SIMPLE      | t0    | ALL  | NULL          | NULL | NULL    | NULL        | 10   | Using where |
|    1 | SIMPLE      | t1    | ref  | key1          | key1 | 131     | test.t0.col | 1    | Using index |
+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+

Here, the optimizer was able to construct ref access.

Controlling the optimization

@@optimizer_switch variable has sargable_casefold flag to turn the optimization on and off. The default is ON.

Optimizer Trace

The optimization is implemented as a rewrite for query's WHERE/ON conditions. It uses sargable_casefold_removal object name in the trace:

      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "sargable_casefold_removal": {
              "before": "ucase(t1.key1) = t0.col",
              "after": "t1.key1 = t0.col"
            }
          },