Why can't hit the index when i use cast or convert in mysql-CodePudding

Edit after @Behrang answer:

Ty for answer! This is my mistake. I mistakenly thought that only one column would reproduce the problems I encountered.

Therefore, I gave a table creation statement with only one column without testing. According to your tips, I tried. When there is only one column, those SQL can hit the index.

So I realized that this scenario did not seem to match my problem.

I tried to add a column, type int, and arrange it in front of the num column, and then the situation described in my question occurred.

CREATE TABLE `test1` (
  `num1` int(11) NOT NULL DEFAULT '0',
  `num` varchar(11) NOT NULL DEFAULT '',
  KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

explain SELECT * FROM test1 WHERE num = CONCAT(1, '');

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
  1,  SIMPLE,      test1, ,           ref, num, num, 35, const, 1, 100.00,

explain SELECT * FROM test1 WHERE num = CAST(1 AS CHAR);

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
  1,  SIMPLE,      test1, ,           ALL, , , , , 4, 100.00, Using where

explain SELECT * FROM test1 WHERE num = CONVERT(1, CHAR);

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
  1,  SIMPLE,      test1, ,           ALL, , , , , 4, 100.00, Using where

Mysql version: 8.0.18

A simple table with 1 column and 1 KEY, the column type is varchar.

CREATE TABLE `test1` (
  `num` varchar(11) NOT NULL DEFAULT '',
  KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i know the mysql whild do the Type Conversion in Expression Evaluation when an operator is used with operands of different types Type Conversion in Expression Evaluation:

The following rules describe how conversion occurs for comparison operations:

1.If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

2.If both arguments in a comparison operation are strings, they are compared as strings.

3.If both arguments are integers, they are compared as integers.

4.Hexadecimal values are treated as binary strings if not compared to a number.

5.If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.

6.If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

7.In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

i use two sqls to test this:

-- sql1
select * from test1 where num = 1;
-- sql2
select * from test1 where num = '1'

i think sql1 will use the rule 7 to make a Type Conversion, and it is true,when i use explain, sql1 has not hit the KEY, the type is ALL:

explain select * from test1 where num = 1;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
  1,  SIMPLE,      test1, ,           ALL,  num, , , , 8, 12.50, Using where

so i try to use concat、cast、convert to make an explicit type Conversion

-- sqlA
select * from test1 where num = CONCAT(1, '');
-- sqlB
select * from test1 where num = CAST(1 AS CHAR);
-- sqlC
select * from test1 where num = CONVERT(1, CHAR);

I think concat、cast、convert can make the operands conversion to char before query, so use them can avoid Type Conversion in Expression Evaluation in mysql, but i found just sqlA can hit the KEY, sqlB & sqlC can't hit the KEY!

i have reviewed the information, but i didn`t figure out why this happen!

can someone tell me why?

CodePudding user response：

I tried all the queries and they all hit the index.

First I recreated your table:

CREATE TABLE `test1`
(
    `num` varchar(11) NOT NULL DEFAULT '',
    KEY `num` (`num`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO test1
VALUES ('1'),
       ('2'),
       ('3'),
       ('4');

Then I ran your queries:

EXPLAIN
SELECT *
FROM test1
WHERE num = '1';

EXPLAIN
SELECT *
FROM test1
WHERE num = 1;


EXPLAIN
SELECT *
FROM test1
WHERE num = CONCAT(1, '');

EXPLAIN
SELECT *
FROM test1
WHERE num = CAST(1 AS CHAR);

EXPLAIN
SELECT *
FROM test1
WHERE num = CONVERT(1, CHAR);

And all were hitting the index:

Select
   Filter
      Full Index Scan (Index scan)

MySQL 8.0.29.

CodePudding user response：

Problem has been resolved！Thanks to @Behrang for the tip！

In the original question, the reason why the single-field table can hit the index is because the sqlB and sqlC are optimized to the following before execution(after optimized, sqlB and sqlC been same sql)：

select `test1`.`num` AS `num` from `test1` where (convert(`test1`.`num` using utf8mb4) = cast(1 as char charset utf8mb4))

Since the field is specified after the select, the Non-Clustered Index generated by the num column is directly used.

When adding other columns to the table, since select * is used, it is necessary to return to the table.In this case, the index cannot be used, so the explains type will been ALL.

The operands of the transformation query can be displayed with the correct transformation, as follows(specified character set):

-- sqlA
explain SELECT * FROM `test1` WHERE num = CONCAT(1, '');
-- sqlB
explain SELECT * FROM `test1` WHERE num = CAST(1 AS char charset utf8);
-- sqlC
explain SELECT * FROM `test1` WHERE num = CONVERT(1 using utf8);

But why need to specify the character set when using CASE or CONVERT, I didn't find the relevant instructions, I hope I can get some more hints.