select t1.id2, t1.id3, t2.name, t3.name
from t1, t2, t3
where t1.id2 = t2.id2 and t1.id3 = t3.id3;select t1.id2, t1.id3,
(select name from t2 where t1.id2=t2.id2),
(select name from t3 where t1.id3=t3.id3)
from t1;这两个sql有什么差别,主要是效率上,其余各个方面如果有的话也都要,谢谢
from t1, t2, t3
where t1.id2 = t2.id2 and t1.id3 = t3.id3;select t1.id2, t1.id3,
(select name from t2 where t1.id2=t2.id2),
(select name from t3 where t1.id3=t3.id3)
from t1;这两个sql有什么差别,主要是效率上,其余各个方面如果有的话也都要,谢谢
解决方案 »
- MYSQL查询问题
- mysql-5.5.25a-winx64 to mysql-5.5.29数据升级
- 这两条sql条件执行在join前还是join后
- 请教一个比较繁琐的数据库查询的问题
- mysql 查询 小于 时间问题
- <MySqlAPI>错误号:2013
- Linux上的mysql4.0.18,用jdbc连接,总是报错: Unsupported character encoding 'UTF-8',怎么办啦
- 如何解决数据远程传输的问题
- 用phppgadmin时出现复制表的问题(复制索引出错)
- MYSQLSLAP性能测试--如何修改参数能实现插入1000条数据再查询?
- MYSQL有排它锁语句吗?
- 100大分,寻高手解决!SQL2000下的MYSQL链接服务器不能执行远程INSERT
select t1.id2, t1.id3, t2.name, t3.name
from t1, t2, t3
where t1.id2 = t2.id2 and t1.id3 = t3.id3;的效率应该高一些
A*B*C
而第二个是
A*(B+C)
好似是第二个快一些啊
我用explain得到的结果,好像也是第二个快不过好像我的快理解的有问题吧,我是把explain得到的rows相乘
结果大的就慢
但我想弄明白,为什么它会快一些,麻烦谁给讲一下对两个sql,mysql具体会做些什么
select t.id1, t.id2, t1.name, t2.name
from t, t1, t2
where t.id1 = t1.id and t.id2=t2.idid sel_type table type poss_key key key_len ref rows Extra
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+------------+------+-------------+
| 1 | SIMPLE | t1 | ALL | PRIMARY | | | | 1 | |
| 1 | SIMPLE | t | ref | Index_1,Index_2 | Index_1 | 5 | test.t1.id | 1 | Using where |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t.id2 | 1 | |
+----+-------------+-------+--------+-----------------+---------+---------+------------+------+-------------+explain
select t.id1, t.id2,
(select name from t1 where t1.id=t.id1),
(select name from t2 where t2.id=t.id2)
from t+----+--------------------+-------+--------+---------------+---------+---------+------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------------+------+-------+
| 1 | PRIMARY | t | ALL | | | | | 4 | |
| 3 | DEPENDENT SUBQUERY | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t.id2 | 1 | |
| 2 | DEPENDENT SUBQUERY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t.id1 | 1 | |
+----+--------------------+-------+--------+---------------+---------+---------+------------+------+-------+
`id1` int(11) default NULL,
`id2` int(11) default NULL,
KEY `Index_1` (`id1`),
KEY `Index_2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE `t1` (
`id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE `t2` (
`id` int(11) NOT NULL default '0',
`name` varchar(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
from t1, t2, t3
where t1.id2 = t2.id2 and t1.id3 = t3.id3;
EXPLAIN EXTENDED select t1.id2, t1.id3,
(select name from t2 where t1.id2=t2.id2),
(select name from t3 where t1.id3=t3.id3)
from t1;
不过,我看,楼主写的两个语句效率都够差的,不过,第一个速度会快一些。
不是吧,两个都差?请赐教一下,还有什么办法吗?
俺加上extended了,也show warnings了
可惜还是看不懂啊第一个
| Level | Code | Message|| Note | 1003 | select `test`.`t`.`id1` AS `id1`,`test`.`t`.`id2` AS `id2`,`tes
t`.`t1`.`name` AS `name`,`test`.`t2`.`name` AS `name` from `test`.`t` join `test
`.`t1` join `test`.`t2` where ((`test`.`t2`.`id` = `test`.`t`.`id2`) and (`test`
.`t`.`id1` = `test`.`t1`.`id`)) |第二个
mysql> show warnings;
| Level | Code | Message| Note | 1276 | Field or reference 't.id1' of SELECT #2 was resolved in SELECT
#1
|
| Note | 1276 | Field or reference 't.id2' of SELECT #3 was resolved in SELECT
#1
|
| Note | 1003 | select `test`.`t`.`id1` AS `id1`,`test`.`t`.`id2` AS `id2`,(sel
ect `test`.`t1`.`name` AS `name` from `test`.`t1` where (`test`.`t1`.`id` = `tes
t`.`t`.`id1`)) AS `(select name from t1 where t1.id=t.id1)`,(select `test`.`t2`.
`name` AS `name` from `test`.`t2` where (`test`.`t2`.`id` = `test`.`t`.`id2`)) A
S `(select name from t2 where t2.id=t.id2)` from `test`.`t` |