当前位置：网站首页>[MySQL] Using join buffer (Block Nested Loop) in left join due to character set in mysql

[MySQL] Using join buffer (Block Nested Loop) in left join due to character set in mysql

2022-08-10 23:12:00 【Tao Shihan】

I found that the index was not used when querying a sql today

I read it carefully many times, and I have added all the indexes that should be added, but it still doesn't work

View with explain

Why does the index fail

Implicit conversion causes index invalidation
With the growth of the table, the data from the where condition is too much, more than 15%, which makes the index invalid (it will cause the CBO calculation to use the index to cost more than the whole table)
Inconsistent character sets cause the index to fail, one utf8 and one utf8mb4

My main problem here is that the character set is inconsistent and the index is invalid

Convert the character set successfully

ALTER TABLE user CONVERT TO CHARSET utf8mb4;

版权声明
本文为[Tao Shihan]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/222/202208102229584170.html