hi all ,
my Fisrt problem was when table Contain Huge Number of rows 1,500,000 + and more.
i decide to Create New Table Called TOp10Post which contain only 10 Post for each users to Fetch Data From Top10Post Table instead of fetching Data from Post Table.
now My New Table should Be:
Top10Post(PotsID int primary Key and Clustered Index) and indexes are: 1-nonclustered Index(UserId,PostID desc) 2- clustered Index on(PostID desc)
and seoond Table is: UsersInContact(ContactID int , UserId uniqueidentifier) and the Indexes on UsersInContact are :
1- Clustered Index(ContactID int , UserId uniqueidentifier) 2- nonclustered Index(ContactID desc).
and My Query should be:
select top 10 Top10Post.PostID from Top10Post inner join UsersInContact on Top10Post.UserId=UsersInContact.UserId where UsersInContact.ContactID=@ContactID Order By Top10Post.PostID desc
this is all my Data, please do u think every thing are Ok?? any more suggestions??
asked Mar 01 '12 at 12:06 PM in Default
The non-clustered index that is the same as the clustered index is either never going to be used, or, could cause issues where it's used instead of the clustered index leading to unnecessary lookup operations. Don't duplicate columns in indexes unless you're changing the order or structure in a way that's likely to change behavior in a positive way.
To be absolutely sure of your indexes, you need to do testing. Just eyeballing stuff doesn't answer the question of whether or not an index is good.
answered Mar 01 '12 at 04:38 PM
Grant Fritchey ♦♦
My big concern is how you are maintaining TOP 10 posts?
And as far as indexes are concerned, we can only do guess work. It would really depend upon the workload and nature of the queries run against these tables. Seems like you have it covered for this query. But that we cannot be sure of until we have the execution plan. Although, index (ContactID desc) seems to be unnecessary.
Also, having a Uniqueidentifier as a part of clustered index may lead to a big fragmented index. So reorganizing/rebuilding of that index should be part of your maintenance plan for optimal throughput.
answered Mar 01 '12 at 12:50 PM
thank u all about great answers , great suggestions.
all people here are great people .
thanks again .
answered Mar 01 '12 at 07:06 PM