How to solve the problem that join cannot walk the index caused by different MYSQL character sets

The main content of this article is to explain "how to solve the problem that join can not go to the index caused by different MYSQL character sets", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the problem that join can't index caused by different MYSQL character sets.

During the conversion of sql, you can find the following cases where the deptid of the s table is converted to utf8mb4, that is, there are different deptid character sets of the associated fields between the two tables in left join.

On ((`SSS`.`d`.`Deptid` = convert (`ppp`.`s`.`Deptid` using utf8mb4)

Close inspection shows that the deptid field of table d is utf8mb4, but the deptid field of table s is utf8.

As a rule of thumb, different character sets of fields when associated on both sides do cause indexing to be impossible, because there is an implicit conversion. At this point, the index on the s table cannot take effect.

At this point, I have a doubt that when the condition d.DEPTID = '00001111' is replaced by s.DEPTID = '00001111', it can select the index, while different character sets still exist, it is found that the reason for choosing the primary key of the d table above is clustered_pk_chosen_by_heuristics. Because it's the primary key? This is not very understandable.

"considered_access_paths": [

{

"access_type": "eq_ref"

"index": "PRIMARY"

"rows": 1

"cost": 1.2

"chosen": true

"cause": "clustered_pk_chosen_by_heuristics"

}

It will be normal after changing the character set to utf8mb4:

+- +-+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | d | NULL | const | PRIMARY INDEX_DEPARTMENT_5 | PRIMARY | 1 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | s | NULL | ref | IND_SHOP_DEPTID | 131c | const | 1 | 100.00 | NULL | + -+-+ "ref_optimizer_key_uses": [ {"table": "`sss`.`department``d`" "field": "Deptid", "equals": "00001111", "null_rejecting": false}, {"table": "`sss`.`department``d`", "field": "Deptid", "equals": "000011'" "null_rejecting": false}, {"table": "`ppp`.`shop``s`", "field": "Deptid", "equals": "`sss`.`d`.`Deptid`" "null_rejecting": false}] / * ref_optimizer_key_uses * /}, {"rows_estimation": [{"table": "`sss`.`department``d`", "rows": 1, "cost": 1 "table_type": "const", "empty": false}, {"table": "`ppp`.`shop``s`", "range_analysis": {"table_scan": {"rows": 998690 "cost": 207849} / * table_scan * /, "potential_range_indexes": [{"index": "PRIMARY", "usable": false "cause": "not_applicable"}, {"index": "IND_SHOP_DEPTID", "usable": true, "key_parts": ["Deptid" "Shopid"] / * key_parts * /}, {"index": "IND_SHOP_DOMAIN", "usable": false "cause": "not_applicable"}] / * potential_range_indexes * /, "setup_range_conditions": [] / * setup_range_conditions * /, "group_index_range": {"chosen": false "cause": "not_single_table"} / * group_index_range * /, "analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "IND_SHOP_DEPTID" "ranges": ["00001111