sql查询语句优化

[TheGodOfGods]

表结构，查询条件如下，自己也写了个查询语句，但总觉得不大好，请教大家是否有更好查询办法

if object_id('[CrowdMember]') is not null
	drop table [CrowdMember]
go
if object_id('[MyUser]') is not null
	drop table [MyUser]
go
/****** 对象:  Table [dbo].[CrowdMember]    脚本日期: 12/27/2010 16:54:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyUser](
	[ID] [int] NOT NULL,						
	[OnLineState] [int] NOT NULL CONSTRAINT [DF_User_JoinState]  DEFAULT ((0)),			--在线状态
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CrowdMember](
	[ID] [int] NOT NULL,
	[CrowdID] [int] NOT NULL,
	[MemberUserID] [int] NOT NULL,
	[JoinState] [int] NOT NULL,
 CONSTRAINT [PK_CrowdMember] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

insert into MyUser
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 1 union all
select 5, 1
insert into [CrowdMember]
select 1, 1, 1, 0 union all
select 2, 2, 1, 0 union all
select 3, 3, 1, 0 union all
select 4, 1, 2, 0 union all
select 5, 2, 2, 0 union all
select 6, 3, 2, 0 union all
select 7, 1, 3, 0 union all
select 8, 2, 3, 0 union all
select 9, 3, 3, 0 union all
select 10, 1, 4, 0 union all
select 11, 2, 4, 0 

--查找CrowdMember中与我同属于一个CrowdID的在线用户ID
SELECT distinct MyUser.ID FROM MyUser, CrowdMember AS MT, CrowdMember AS MS  
WHERE MyUser.OnLineState = 1 AND MyUser.ID = MT.MemberUserID 
AND MT.CrowdID = MS.CrowdID AND MS.MemberUserID != MT.MemberUserID AND MS.MemberUserID = 2

/*
ID
-----------
1
3
4

(3 行受影响)
*/

[昵称被占用了]

语句没问题

数据量大的话增加必要的索引

[昵称被占用了]

先增加如下两个索引

 create ind_CrowdMember_CrowdID on CrowdMember(CrowdID)
go
 create ind_CrowdMember_MemberUserID on CrowdMember(MemberUserID)
go

[TheGodOfGods]

谢谢Haiwer,
不过我还是有个疑问，在MemberUserID列上加索引，查询条件“AND MS.MemberUserID != MT.MemberUserID”为什么没有导致索引失效？

[TheGodOfGods]

我觉得索引未失效的原因是因为在查询计划里边，关于MemberUserID的索引扫描下“已排序”标识为“TRUE"