向大侠求教：加了日期排序字段后，查询速度剧减，我该如何优化？谢谢！！！

[jsntcw]

向大侠求教：加了日期排序字段后，查询速度剧减，我该如何优化？谢谢！！！

Order by 后面跟上日期型字段a.postDate速度剧减，已经在该字段上加了索引，
还是很慢啊！这不是要命吗？如何跨越他？


select top 20
a.marketId,
a.subject,
b.companyName

from markets a inner Join company b on a.UserId=b.UserId

where

a.marketId = (select top 1 marketId from markets where userId=a.UserId order by marketId desc)
--not exists (select 0 from markets where userId=a.UserId and marketId < a.marketId)

Order by b.userType desc , a.postDate desc

[jsntcw]

up

[jsntcw]

up

[jsntcw]

up

[wudan8057]

select top 20
a.marketId,
a.subject,
b.companyName
from markets a inner Join company b on a.UserId=b.UserId
where a.marketId = (select min(marketId) from markets where userId=a.UserId)
Order by b.userType desc , a.postDate desc
在markets表的marketid上建立索引.


最好把你的表结构贴出来

[jsntcw]

表结构如下：marketid 上已建索引

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Markets]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Markets]
GO

CREATE TABLE [dbo].[Markets] (
[MarketId] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[CategoryId] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[type] [tinyint] NOT NULL ,
[Subject] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Keys] [varchar] (60) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Specification] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Pack] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DateCode] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Qty] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OfferExpire] [smallint] NOT NULL ,
[Note] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PostDate] [datetime] NOT NULL ,
[ViewTimes] [int] NOT NULL ,
[Audit] [bit] NOT NULL ,
[HiddenContact] [bit] NOT NULL
) ON [PRIMARY]
GO

[jsntcw]

索引部份：
ALTER TABLE [dbo].[Markets] WITH NOCHECK ADD
CONSTRAINT [PK_Markets] PRIMARY KEY CLUSTERED
(
[MarketId] DESC
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Markets] WITH NOCHECK ADD
CONSTRAINT [DF_Markets_UserId] DEFAULT (0) FOR [UserID],
CONSTRAINT [DF_SupplyDemand_ProductID] DEFAULT (0) FOR [ProductID],
CONSTRAINT [DF_Markets_CategoryId] DEFAULT (0) FOR [CategoryId],
CONSTRAINT [DF_SupplyDemand_type] DEFAULT (1) FOR [type],
CONSTRAINT [DF_SupplyDemand_Price] DEFAULT (0) FOR [Price],
CONSTRAINT [DF_SupplyDemand_OfferExpire] DEFAULT (0) FOR [OfferExpire],
CONSTRAINT [DF_SupplyDemand_PostDate] DEFAULT (getdate()) FOR [PostDate],
CONSTRAINT [DF_SupplyDemand_ViewTimes] DEFAULT (0) FOR [ViewTimes],
CONSTRAINT [DF_Markets_check] DEFAULT (0) FOR [Audit],
CONSTRAINT [DF_Markets_HiddenContact] DEFAULT (0) FOR [HiddenContact]
GO

CREATE INDEX [IX_Markets_CategoryId] ON [dbo].[Markets]([CategoryId] DESC ) ON [PRIMARY]
GO

CREATE INDEX [IX_Markets_UserID] ON [dbo].[Markets]([UserID] DESC ) ON [PRIMARY]
GO

CREATE INDEX [IX_Markets_PostDate] ON [dbo].[Markets]([PostDate] DESC ) ON [PRIMARY]
GO

CREATE INDEX [IX_Markets_Keys] ON [dbo].[Markets]([Keys]) ON [PRIMARY]
GO

[jsntcw]

高手帮忙啊！

谢谢

[mengzulin]

一年有54周,所以不用月份了

[mengzulin]

Sorry,I send wrong.

[mengzulin]

不会，可能是你取到了加锁了的记录．

[拓狼]

表markets的UserID上建立了索引，但是company的UserId上没有建立索引，因为在查询的时候是用这两个表的Userid做连接的，由于只有一个索引，另一个没有索引，查询时company要进行全表扫描，故建议在company的UserID上也建立索引，速度会提高很多

[phantomMan]

如果你需要经常取最新新闻时,此时,如果数据按段比较明显,比如上面你要查询每天每刻的最新记录,这是在时间上建立聚簇索引是最好的;

[张海霖]

联合聚集索引