在left join使用强制索引无效

[Ray_Zhang]

有两张表，表A和表B
在B表的name字段上有索引，我的查询语句
SELECT A.*，B.description
FROM A
LEFT JOIN B FORCE INDEX(Index_4) ON A.name=B.name


用EXPLAIN查看了，并没有使用我强制的索引
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "A" "ALL" \N \N \N \N "2841735" "Using where"
"1" "SIMPLE" "B" "ALL" \N \N \N \N "928081" ""

但当改写成子查询就可以使用索引了
SELECT A.*，（select B1.description from B as B1 where B1.name=B.name ) as description
FROM A
LEFT JOIN B FORCE INDEX(Index_4) ON A.name=B.name

"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "A" "ALL" \N \N \N \N "2841750" "Using where"
"1" "PRIMARY" "B" "index" \N "Index_4" "53" \N "928081" "Using index"

这是有什么原因

[wwwwb]

SHOW CREATE TABLE A;
SHOW CREATE TABLE B;

[rucypli]

A.name和B.name字段类型不一样吗

[wwwwb]

show index from a
show index from b
有多个索引？

[Ray_Zhang]

A表的

PRIMARY 1 pk
name_idx 1 name

B表的

PRIMARY 1 pk
FK3DCD3A0ED4616AFF 1 cate_pk
Index_4 1 name
Index_5 1 data_type
Index_6 1 update_date

[wwwwb]

测试了一下，没有这种情况，MYSQL版本多少 ？

[wwwwb]

除非指定索引中没有相应的字段，才会不使用指定的索引

[Ray_Zhang]

5.0.26-community-nt-log

[小小小小周]

不会出现这个情况，
楼主把具体建表语句和insert 语句贴出来。