Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Unanswered: Search using indexes

    I'm trying to speedup some queries that I have.

    Say I have a table with an index: field1, field2, field3

    When I search the table if I have something like this:

    Select *
    From Table
    Where field1='yadda'
    And field2='yadda'
    And field3='yadda'

    Then, you can say that I used the index to search. But what If I add another field in the where clauses, like:

    Where field1='yadda'
    And field2='yadda'
    And field3='yadda'
    And field4='yadda'

    Will adding the extra field slow the query down? Or will it pretty much run as fast as it did if I were using the fields in the index?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Since you are using "select *", the given index will not be considered as a covering index. In that case, and given the absence of any alternate indexes, it should run in just about the same time. The query plans will look very similar.

  3. #3
    Join Date
    Nov 2007
    Posts
    7
    I'm not sure what you're saying.

    What if I had a query like this:

    select field1, field2, field3, field4, field5
    From table
    Where field1='yadda'
    And field2='yadda'
    And field3='yadda'
    And field4='yadda'

    Basically, are you saying that if I don't use a covering index then the speeds will be the same?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If there is no covering index, there will necessarily be a bookmark lookup (I think this changed to a join in 2005). Add more steps, and you are bound to need more time. Look over the query plans, and see for yourself.

Posting Permissions

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