这个sql语句如何优化？

[带头大哥]

select c.cabName,r.motorName ,
(
select
p.poolName
from
NodeInfo n,PoolInfo p,Pool_node pn
where
n.nodeID = ?
and
p.poolID=pn.poolID
and
pn.nodeID =n.nodeID
)
as
poolName, n.* from NodeInfo n,CabinetInfo c, MotorRoomInfo r
where
n.nodeID = 6 and n.cabinetID =c.cabinetID and c.roomID = r.roomID;

[Rotel-刘志东]

给出表结构和具体的查询怎样的数据。

[ACMAIN_CHM]

create index xx1 on NodeInfo(nodeID,cabinetID);
create index xx2 on CabinetInfo(cabinetID,roomID);
create index xx3 on CabinetInfo(roomID,cabinetID);
create index xx4 on MotorRoomInfo(roomID);
create index xx5 on NodeInfo(poolID);

[小小小小蜗牛]

explian

[带头大哥]

我的sql中有很多and，会影响效率吗？怎样把这个sql变的简单一些？

[diffmaker]

[Quote=引用 2 楼 的回复:]

create index xx1 on NodeInfo(nodeID,cabinetID);
create index xx2 on CabinetInfo(cabinetID,roomID);
create index xx3 on CabinetInfo(roomID,cabinetID);
create index xx4 on MotorRoomInfo(roomID);……
[/Quote]

索引

[diffmaker]

select 
	c.cabName,
	r.motorName,
	p.poolName,
	n.*
from
	NodeInfo n inner join PoolInfo p inner join Pool_node pn inner join CabinetInfo c inner join MotorRoomInfo r
on
	n.cabinetID =c.cabinetID and c.roomID = r.roomID and p.poolID=pn.poolID and pn.nodeID =n.nodeID
where
	n.nodeID = 6

这样写可以吗？

[rucypli]

把复杂的sql拆成简单的单条sql

[tigerwithwing]

建议做个临时表。定时往里写数据，把整理后的数据结果存起来。虽然不是时时的数据，但是查询效率高。