not using index

Question

Post reply

not using index

sqlpro

Hall of Fame

Points: 3859
More actions
September 6, 2005 at 5:19 pm

#115154

Hi friends

i have table with foreign key and when i query on that field (like below) do we normally expect it use index scan or index seek ?

select
assid,fk_staffid from assignment where fk_staffid='assole'
abv query using index scan when i checked in query analyzer !! when im expecting it to see index seek ? or am i missing something!!
Thanks for ur ideas

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sqlpro Hall of Fame Points: 3859 More actions · Answer 1

looks like i need to create an index on this field separately

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Oh, you did not?

That's deinetely it!

_____________
Code for TallyGenerator

sqlpro Hall of Fame Points: 3859 More actions · Answer 3

actually i thought when we create a foreign key it automatically creates an index

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Foreign key is just constraint, not an index.

_____________
Code for TallyGenerator

sqlpro Hall of Fame Points: 3859 More actions · Answer 5

I agree Sergiy.

probably the reason for my misunderstanding is "if you query on a primary key field it uses Index seek, right" so i though even foreign key will have similar effect.

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 6

If you query on a foreign key and there is no index then you will probably get a Clustered Index Scan.

The question I have is, let us suppose that you don't actually query the foreign key at all, it is just there as a cross reference to a look-up table.

Is it worth creating an index on that foreign key in this instance? I've done some playing around and even if the index on the foreign key is used it seems to increase the cost of the select query, not reduce it

sqlpro Hall of Fame Points: 3859 More actions · Answer 7

>>Is it worth creating an index on that foreign key in this instance? I've done some playing around and even if the index on the foreign key is used it seems to increase the cost of the select query, not reduce it

but it seems it reduced time in my case David.i was profiling a SP which was taking 243ms ,which was using a foreign key field in WHERE condition.after i created the index on FK field now SP executes in 63ms!!