Starting from , expressions in the form
are sargable if key_col uses either the utf8mb3_general_ci or utf8mb4_general_ci collation.
UCASE is a synonym for UPPER so is covered as well.
Sargable means that the optimizer is able to use such conditions to construct access methods, estimate their selectivity, or perform partition pruning.
Note that ref access is used.
An example with join:
Here, the optimizer was able to construct ref access.
The variable has the flag sargable_casefold to turn the optimization on and off. The default is ON.
The optimization is implemented as a rewrite for a query's WHERE/ON conditions. It uses the sargable_casefold_removal object name in the trace:
: Make optimizer handle UCASE(varchar_col)=...
An analog for is not possible. See : Make optimizer handle LCASE(varchar_col)=... for details.
This page is licensed: CC BY-SA / Gnu FDL
UPPER(key_col) = expr
UPPER(key_col) IN (constant-list)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 |
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+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 |
+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+"join_optimization": {
"select_id": 1,
"steps": [
{
"sargable_casefold_removal": {
"before": "ucase(t1.key1) = t0.col",
"after": "t1.key1 = t0.col"
}
},