Post reply

not using index

  • 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

  • September 6, 2005 at 6:06 pm

    #587962

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

  • September 6, 2005 at 6:29 pm

    #587963

    Oh, you did not?

    That's deinetely it!

    _____________
    Code for TallyGenerator

  • September 6, 2005 at 6:34 pm

    #587964

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

  • September 6, 2005 at 8:13 pm

    #587972

    Foreign key is just constraint, not an index.

     

    _____________
    Code for TallyGenerator

  • September 6, 2005 at 8:20 pm

    #587974

    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.

  • September 7, 2005 at 2:35 am

    #588010

    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

  • September 7, 2005 at 3:01 pm

    #588263

    >>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!!

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

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