MySQL utf8 and utf8mb4 indexing problems?

CREATE TABLE test1 (
id int (11) NOT NULL AUTO_INCREMENT,
name varchar (20) DEFAULT NULL,
code varchar (50) DEFAULT NULL,
PRIMARY KEY ( id ),
KEY idx_code ( code )

CREATE TABLE test2 (
id int (11) NOT NULL AUTO_INCREMENT,
name varchar (20) DEFAULT NULL,
code varchar (50) DEFAULT NULL,
PRIMARY KEY ( id ),
KEY idx_code ( code )

1.
explain select * from test1 join test2 on test1.code = test2.code where test1.name = "xxx";
explain select * from test1 join test2 on test1.code = test2.code where test2.name =" xxx";

Why the first statement, test1, can use the index, and the second statement, test1, does not use the index.

2.
explain select * from test2 join test1 on test1.code = test2.code where test1.name = "xxx";
explain select * from test2 join test1 on test1.code = test2.code where test2.name =" xxx";
Why test1 can use the index in the first statement and test1 can not use the index in the second statement.

in the above two cases, the code field of test1 does not need an index. When can the test1 field be indexed?

Menu