The Ultimate Guide to High Availability with MariaDB

Sargable UPPER

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

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.

h2. Example

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

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: explain select * from t0, t1 where UPPER(t0.col)=UPPER(t1.col)

h2. Controlling the optimization

@@optimizer_switch has sargable_casefold flag.

h2. Optimizer Trace

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.