View Poll Results: Which Indexing method is better if any?

Voters
7. You may not vote on this poll
  • Covered Index on all relavent fields

    6 85.71%
  • Individual Index on each relavent field.

    0 0%
  • Doesn't matter

    1 14.29%
Results 1 to 8 of 8

Thread: Indexes

  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: Indexes

    Say you have a table with 10 fields, 50,000-100,000 records, and 1 primary key field.

    Is there any performance difference between creating a "covered" index and versus creating 9 individual indexes (not 10 b/c i'm assuming the PK field will already have an index created for it), one for each non key field.

  2. #2
    Join Date
    May 2003
    Posts
    23
    What is a "covered" index?

  3. #3
    Join Date
    Mar 2002
    Posts
    192
    A covered Index is an index which includes many columns in it. For example if you have an application which can search on 5 out of 10 fields you could create an index on those 5 fields and the result is supposed to be quicker searches on those 5 fields.

  4. #4
    Join Date
    May 2003
    Posts
    23
    Then I guess it might depend on how often rows are inserted, and how often these columns are updated, since that is when index rows would be inserted or updated.

    It sounded like you were suggesting putting all the columns of the table in one index -- this would not accomplish anything, would it?

    I see what you are saying about combining some columns in an index, particularly if your search would be filtering on more than one of the columns.

    It's difficult to make a suggestion without more info on activity, column size, and filtering methods.

  5. #5
    Join Date
    Mar 2002
    Posts
    192
    Assume that you'll be reading most of the time. Given that is there a difference between the two methods.. If any?

  6. #6
    Join Date
    May 2003
    Location
    Athens Greece
    Posts
    13
    Do we speak about read or write operations here?

    If we speak read, then it's all coming about what queries are going to be used.


    If there's gonna be a SELECT statement that will be utilizing a scope that will use a WHERE with 5 fields, the optimum is to have an index that will cover those 5 fields instead of having those 9 seperate indexes.

    As for the write operation I am not that sure that there'll be any differences anyways.

    I think it all comes to the query optimizer really and the execution plan.

  7. #7
    Join Date
    Jul 2002
    Location
    IA
    Posts
    28

    Covered queries

    I've always thought you should cover the columns in a popular where clause.

    In a few cases where the data is inserted or updated more than read, then fewer indexes is better.
    Thanks,
    Jason

  8. #8
    Join Date
    Apr 2003
    Posts
    30
    I think it depends on your query.
    Also, the index tuning wizard may helpful in your case.

Posting Permissions

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