x

who can help Me by chosing appropriate index?

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??
more ▼

asked Mar 01, 2012 at 12:06 PM in Default

qamooos gravatar image

qamooos
91 4 5 6

Everyone here would help you with pleasure, if you can supply every detail you are asked for. Thanks.
Mar 01, 2012 at 12:35 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.
more ▼

answered Mar 01, 2012 at 04:38 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
97.9k 19 21 74

At the risk of being nit-picky, there are some edge cases where a nonclustered index is more effecient for a query than a clustered one even over the exact same column. But those mostly involve aggregates of the indexed column itself with fairly wide rows. I can't think of a time when it actually made sense in practice to have a nonclustered index over the same columns as the clustered index.
Mar 01, 2012 at 06:57 PM TimothyAWiseman
Absolutely. If you're doing a count of the rows, and you're not referencing the data, there's a good shot that a nonclustered index will get picked over a clustered index even though both are on the same column. Same with scans. The optimizer is aware of the size of indexes as part of the optimization process, so if it can use a smaller index, it will. But, it is an edge case.
Mar 01, 2012 at 07:09 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Mar 01, 2012 at 12:50 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Hi thank u about answer :)

lsiten mY Borther.

what im trying to do , when the User Enter new Post, is will check COUNT() PostID For given user , if the COUNT=10 , then it delete first ,else do no thing !!

and why do u think index on ContactID is not unnecessary, although the Query Depend On ContactID ???? thank u again
Mar 01, 2012 at 01:44 PM qamooos

That is because you already have a clustered index on ContactID. So for this query it may be unnecessary.

As far as your top 10 post implementation is concerned, I am not able to understand properly. Is it handled in a TRIGGER? Is this process duplicating the records both in POST and Top10Post? Whatever the answers are, please make sure that you implement it correctly and safely.
Mar 01, 2012 at 02:05 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

thank u all about great answers , great suggestions.

all people here are great people .

thanks again .
more ▼

answered Mar 01, 2012 at 07:06 PM

qamooos gravatar image

qamooos
91 4 5 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1943

asked: Mar 01, 2012 at 12:06 PM

Seen: 712 times

Last Updated: Mar 01, 2012 at 07:09 PM