inner join and Index

Question

hi My Freinds.

i have problem on Using Index with Inner join Query( on Merge Table) , i have putting Suitable indexs but still bad:

i want to Fetch Last top 10 Post Only For People In MyFavouriteContact( By Given FavouriteContactID) as follow:

FavouriteContact(FavouriteContactID,UserId)    

Users(UserId,Name,dateofBirth,Tel)

Merge_Users_FavouriteContact(FavouriteContactID,UserId,Merge_ID)

Post(PostID,PostDate,PostBody,PostDate,UserId)

==========================================================================================

select Top 10 PostID from Post inner join Merge_Users_FavouriteContact

on Post.UserId=Merge_Users_FavouriteContact.UserId

where Merge_Users_FavouriteContact.FavouriteContactID=@FavouriteContactID

this Query Fetching Post from Users , and Only For Users Added in My Contact (Merge_Users_Contact)

===========================================================================================

 I have setting non-Clustered Index on:

1- IX_Merge_Users_FavouriteContact_1(FavouriteContactID)

2- IX_Merge_Users_FavouriteContact_2(UserId)

3-IX_Post(UserId)

all other My Scenario Like this scenario , if its solved all other will be solved.

thanks again.

Answer 1

You neglected to mention whether or not you have or might have an ORDER BY clause on the query; this is important because the ORDER BY clause can have a huge impact on the performance of this particular query.  Also, please run these two queries and get back to us with the results:

select count(*)
from post
inner join merge_Users_FavouriteContact
  on post.UserId = merge_Users_FavouriteContact.userId
where merge_Users_FavouriteContact.favouriteContact_ID
    = @favouriteContactId;

select count(distinct favouriteContactId)
from merge_Users_FavouriteContact;

Answer 2

hi thank u about answer, i have tried ur solution but the problem still exists.

im using  Order By Post.PostID Desc

i think the problem on Index. any help?

Answer 3

hi thank u about answer, i have tried ur solution but the problem still exists.

im using  Order By Post.PostID Desc

i think the problem on Index. any help?

Interesting; I wasn't offering my request for diagnostic information as a solution to your problem.  What I am interested in are the two cardinalities that might be returned from the two queries that I provided.  If you could run those two queries and provide the results, that might be helpful.