有个表 t 含如下字段
ForumId,PubState,PubFlag,LockFlag
主键是TopicID有个如下的查询
select count(1) as cn from t a
where forumid=161 and a.lockflag='0' and isnull(a.PubFlag,'0')='0' and isnull(a.pubState,'0')='0'
再不加and isnull(a.pubState,'0')='0'条件时,查询执行时间2秒之内
一旦加上 此条件查询的执行时间就在1分钟以上
难道sql把and条件当场了or条件,先分别计算后再来合并结果的吗。完全不解!
请各位大牛指点一,二[ForumId] [int] NULL,[LockFlag] [nchar](1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF__BL_Topic__LockFl__7928F116] DEFAULT ('0'),[PubState] [nchar](1) COLLATE Chinese_PRC_CI_AS NULL DEFAULT ('0'),[PubFlag] [nchar](1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT
[DF__BL_Topic__PubFla__7CF981FA] DEFAULT ('0'),CREATE NONCLUSTERED INDEX [Index_3] ON [dbo].[BL_Topic]
( [ForumId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
ForumId,PubState,PubFlag,LockFlag
主键是TopicID有个如下的查询
select count(1) as cn from t a
where forumid=161 and a.lockflag='0' and isnull(a.PubFlag,'0')='0' and isnull(a.pubState,'0')='0'
再不加and isnull(a.pubState,'0')='0'条件时,查询执行时间2秒之内
一旦加上 此条件查询的执行时间就在1分钟以上
难道sql把and条件当场了or条件,先分别计算后再来合并结果的吗。完全不解!
请各位大牛指点一,二[ForumId] [int] NULL,[LockFlag] [nchar](1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF__BL_Topic__LockFl__7928F116] DEFAULT ('0'),[PubState] [nchar](1) COLLATE Chinese_PRC_CI_AS NULL DEFAULT ('0'),[PubFlag] [nchar](1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT
[DF__BL_Topic__PubFla__7CF981FA] DEFAULT ('0'),CREATE NONCLUSTERED INDEX [Index_3] ON [dbo].[BL_Topic]
( [ForumId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
有此设置,为什么还要ISNULL呢?不知道你的问题所在
不过,你可以先UPDATE该列的NULL值为0再处理啊 :-)