有两张表,表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"这是有什么原因
在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"这是有什么原因
show index from b
有多个索引?
name_idx 1 nameB表的PRIMARY 1 pk
FK3DCD3A0ED4616AFF 1 cate_pk
Index_4 1 name
Index_5 1 data_type
Index_6 1 update_date
楼主把具体建表语句和insert 语句贴出来。
SHOW CREATE TABLE B;