Results 1 to 4 of 4

Thread: Indexing

  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Indexing

    If I do an update (in a trigger) on a table based on two columns, which way is the best to make index

    For example

    update TABLE1
    set COL1 = 1
    where COL2 = 1 and COL3 = 0

    Should I then make one index on both the columns or should I make two seperate indexes?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what do your benchmark test as actual results for both cases?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Posts
    81
    I thought that this was a common issue. When you make a SQL query on two columns, is it more advantageous to use a single index or is it better with two separate ones?

    I have usualy made two indexes when I ask seperately, like
    when
    A=1 and
    B=2
    then I have made an index at column A and another index at column B

    When I query like
    when (A,B) in (1,2)
    I normally create one index (on Column A and B).

    But is that the right way or should I also in the first example make it like one index?

    Of course, I can try making a large update and see how long time it takes with the two different methods, but I just assumed, that this was a common issue, which I just didn't knew the answer to.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It all depends, if you always use both columns in the where clause the one index on both will work best. If you sometimes use one column or the other in the where clause then use two indexes
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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