Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    17

    Unanswered: how to Create the Optimize Index

    hi..all
    i have a question,
    if i work with my table_1, i always using where condition
    Code:
    Select * from Table_1 where field_a = @value_a and field_b = @value_b and field_c = @value_c
    field_a,field_b and field_c is not primary key.

    how i create the Optimize index ???
    should i create 3 indexs for each field ?
    index for field_a, index for field_b and index for field_c

    Or

    i just create 1 index
    index for field_a,field_b,field_c



    thank's
    Jigu

  2. #2
    Join Date
    Oct 2009
    Posts
    27
    Clustered indexes are good for range searches.Non-clustered indexes are good for random searches.There can be only one clustered index per table and multiple non-clustered indexes.
    You are not searching a range of values, So you can do the first choice "create 3 indexs for each field".

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by sql-programmers View Post
    Clustered indexes are good for range searches.Non-clustered indexes are good for random searches.There can be only one clustered index per table and multiple non-clustered indexes.
    You are not searching a range of values, So you can do the first choice "create 3 indexs for each field".
    Mr. Programmers, question:

    If he were always using those 3 fields in the where clause would it be better to use a composite index, or on the separate fields and they should cross ? I typically use separate indexes unless the composite will always cover, is this a good practice ?

    Thanks

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The best index for the query you supplied would be one containing all 3 columns, as long as, the columns are not a one to one match, meaning that they would help to further filter your data.
    Dave

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    jigujigu2, The only thing I would add to dav1mo's suggestion is to put the keys in order from most unique to least unique. This will allow the row to be isolated faster. For example, if you have

    100,000 rows in a table

    Key1 has 10 distinct values (10,000 rows per value on average)
    Key2 has 1000 distinct values (100 rows per value on average)
    Key3 has 99,999 distinct values (almost but not quite unique)

    In this case I would make a single index with KEY3, KEY2 then KEY1.

    With this order Key3 would be found first and then only 1 or 2 rows would need to be checked for Key2 and Key1. If the order was reversed (KEY1, KEY2, KEY3), KEY1 would be found first, than then 10,000 rows would need to be searched for KEY2 and the 100 rows would need to be searched for KEY1.

  6. #6
    Join Date
    Nov 2009
    Posts
    17
    many thank's

    Jigujigu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •