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
.
UCASE
is a synonym for UPPER
so it is covered as well.
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" } },
References
- MDEV-31496: - aaa