Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
Description
In some cases an index on a column cannot be used by the range optimizer because of data type mismatch.
Example 1
In this example the index on the VARCHAR column cannot be used for integer lookups:
CREATE OR REPLACE TABLE t1 (a VARCHAR(10), KEY(a)); |
DELIMITER $$
|
FOR i IN 1..99 |
DO
|
INSERT INTO t1 VALUES (i); |
END FOR; |
$$
|
DELIMITER ;
|
EXPLAIN SELECT * FROM t1 WHERE a=1; |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | a | a | 13 | NULL | 99 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set, 1 warning (0.000 sec)
|
Example 2
In this example the index cannot be used because the field gets wrapped into a CONVERT() function during argument character set aggregation, which makes the index not applicable:
CREATE OR REPLACE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, KEY(a)); |
DELIMITER $$
|
FOR i IN 1..99 |
DO
|
INSERT INTO t1 VALUES (i); |
END FOR; |
$$
|
DELIMITER ;
|
EXPLAIN SELECT * FROM t1 WHERE a=_utf8mb3'1' COLLATE utf8mb3_bin; |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | a | 13 | NULL | 99 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
Let's make it easier to analyze
Under terms of this tasks we'll add SQL notes to SELECT and other statements using the range optimizer:
- when an index cannot be used due to data type mismatch
- when an index cannot be used because the column get wrapped into CONVERT()
Additionally, let's print those notes in the Slow Query Log if log_slow_verbosity=query_plan,explain is set.
This will make it easier to catch and analyze sub-optimal queries.
Attachments
Issue Links
- causes
-
MDEV-32388 MSAN / Valgrind errors in Item_func_like::get_mm_leaf upon query from partitioned table
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-