有两张表,表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"这是有什么原因
解决方案 »
- mysql中为什么调用这个存储过程会报错
- MySQL创建表出现问题,请高手帮忙!!
- mysql报1064错误
- shell命里面里面 删除二个月前备份的数据?
- 比较天数
- Unknown column 错误
- 求救:MySQL数据导入导出问题!!!我发现MySQL里的中文不能用直接的中文表示,导出之后还是用什么� 表示,请问怎样才能正常导出;
- 现在编写数据库(服务器-浏览器模式)的主要工具是什么? 是ASP吗?
- 诸位,偶的 www.help2u.org 开张了
- 大神求教,phpMyAdmin显示错误
- SQL函数在MS SQL SERVER中可以mysql不行!
- 如何用PHPMyAdmin导出大数据
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;