explain
select t.Id,c.role from
(select distinct t1.Id, t1.ip, t1.lastaccess,
from SessionLog t1, SessionLog t2
where
and t1.ip=t2.ip
and t1.id != t2.id) as t,
role c
where
t.Id=c.id请这条语句到底有什么问题, 得不到执行计划
在linux 写着(copy to tmp)
select t.Id,c.role from
(select distinct t1.Id, t1.ip, t1.lastaccess,
from SessionLog t1, SessionLog t2
where
and t1.ip=t2.ip
and t1.id != t2.id) as t,
role c
where
t.Id=c.id请这条语句到底有什么问题, 得不到执行计划
在linux 写着(copy to tmp)
select t.Id,c.role from
(select distinct t1.Id, t1.ip, t1.lastaccess,
from SessionLog t1, SessionLog t2
where
and t1.ip=t2.ip
and t1.id != t2.id) as t,
role c
where
t.Id=c.id
语法错误。
select t.Id,c.role from
(select distinct t1.Id, t1.ip, t1.lastaccess,
from SessionLog t1, SessionLog t2
where t1.ip=t2.ip
and t1.id != t2.id) as t,
role c
where
t.Id=c.id 我去掉了好多内容, 这些东西是不能直接拿出来的哦 总体就是这样的哦
select t.Id,c.role from
(select distinct t1.Id, t1.ip, t1.lastaccess,
from SessionLog t1, SessionLog t2
where t1.ip=t2.ip
and t1.id != t2.id) as t,
role c
where
t.Id=c.id
(select distinct t1.Id, t1.ip, t1.lastaccess, from SessionLog t1, SessionLog t2 where
t1.ip=t2.ip and t1.id != t2.id) as t, role c
where
t.Id=c.id 没有啦, 请看 那是一个查询 表 , 是对称的哦
(select distinct t1.Id, t1.ip, t1.lastaccess, from SessionLog t1, SessionLog t2 where
t1.ip=t2.ip and t1.id != t2.id) as t, role c
where
t.Id=c.id
(select distinct t1.Id, t1.ip, t1.lastaccess, from SessionLog t1, SessionLog t2 where
t1.ip=t2.ip and t1.id != t2.id) as t, role c
where
t.Id=c.id
(select distinct t1.Id, t1.ip, t1.lastaccess from SessionLog t1, SessionLog t2 where
t1.ip=t2.ip and t1.id != t2.id) as t, role c
where
t.Id=c.id 重新编译 还是不知道真么回事,处于等待中
(select distinct t1.Id, t1.ip, t1.lastaccess from SessionLog t1, SessionLog t2 where
t1.ip=t2.ip and t1.id != t2.id) as t
特别是这个distinct 导致执行计划基本失败 他不能找到对应的计划 只会 (copy to tmp table)
楼上,这是根本原因
甚至强加索引 也大大慢于group 语句
explain
select t.Id,c.role
from (
select distinct t1.Id, t1.ip, t1.lastaccess
from SessionLog t1, SessionLog t2
where t1.ip=t2.ip
and t1.id != t2.id
) as t, role c
where t.Id=c.id 显示结果是什么?
无休止等待