Post reply

3 indexes in 1 fields of a table

  • September 10, 2009 at 9:26 am

    #204511

    Whether it is advisable to create 3 indexes in 1 fields in a table.? whether it will speed up the performance or it will degrade the performance .?

    Pls advice me.?

  • September 10, 2009 at 12:14 pm

    #1051863

    we need some more information, do mean you want to create 3 separate indexes.

    col a

    col b

    col c

    or

    a composite index on a,b,c?

    if its a,b,c always put the most selective of the col in order, so if a and b are uniuque foreign keys then a,b,c will work well.

    also keep in mind that it will only work if you are searching something like where col = a or where col = a and col =b

    not sure if that answers your ? I am kinda of guessing

  • September 10, 2009 at 11:34 pm

    #1052072

    Hi

    thanks for this information. actually i will give a example

    Table name : table1

    Columnname: status, accountno,number,ordername

    index created:

    Index_nameType Fields

    A1 Clustered index status, accountno,number,ordername

    B1 Non-Clustered index status, accountno

    C1 Non-Clustered index Accountno,status

    Here are the status, accountno has 3 indexes whether it will upgrade or degrade the performance.?

  • September 10, 2009 at 11:38 pm

    #1052073

    Hi

    thanks for this information. actually i will give a example

    Table name : table1

    Columnname: status, accountno,number,ordername

    index created:

    Index_nameType Fields

    A1 Clustered index status, accountno,number,ordername

    B1 Non-Clustered index status, accountno

    C1 Non-Clustered index Accountno,status

    Here are the status, accountno has 3 indexes whether it will upgrade or degrade the performance.?

  • September 14, 2009 at 1:39 pm

    #1053428

    ok you have a couple of choices.

    The index on B1, this is not needed, these should be picked up using the clustered index if you search on status,accountno then the is will likely use the clustered index since it already in the correct order.

    I would create the clustered index first and then check your executon plan make sure it s being used, more then likely it will be. then let it run a while make sure its even worth while adding the extra index....

    careful though make sure your clustered index has the most select col first, my guess is the status col will not be selective enough..if i had to guess you need to build it like the following

    Accountno,number,ordername,status....but that is just a guess without seeing the data we really don't know....again more then likely the a clustered index seek will be used to cover most of searches.

    You will ALWAYS take a hit on inserts when you add the extra non clustered index...so depending on the index use you may end up wasting more time on the inserts then will gain on the seeks or scans....so in this case start with clustered index and then monitor its use

Viewing 5 posts - 1 through 4 (of 4 total)

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