Post reply

index-question

  • January 22, 2006 at 9:09 pm

    #90514

    hi!

    i have a table (TBL) which has two data colums (COL1, COL2) in which i want to search for @a, and an ID column which i want to get. my query is the next:

    select ID from TBL where COL1= @a or COL2= @a

    i created a unique nonclustered index for COL1, and i create a nonclustered (and not unique) index for COL2 and ID together.

    i need some advice if it was a good choise or i have to change something.

    thanks for your answers.

  • January 22, 2006 at 9:51 pm

    #616318

    Depending on whether the table will be used in a join with other tables, and assuming that ID is the join key, then possibly you could make it the clustered index. Since you are searching on both attributes COL1 and COL2, then both can be nonclustered indexes.

    Note that the selectivity of the data will determine if you should consider an index. The best thing for you is to experiment to see which would give you the best performance.

  • January 22, 2006 at 11:25 pm

    #616321

    Hi

    It would be better if u had created a composite index on those columns as it would increase your performance.

    sunder..

  • January 22, 2006 at 11:36 pm

    #616322

    using composite indexes does the performance depend on the columns order in the definition?

    if the answer is yes, what wolud be the right order to my query? COL1, COL2, ID?

    another note. on COL1 there is a uniqe constraint/ index. so if i put into this index COL2 and ID, COL1 uniqueness is over, isnt it?

  • January 23, 2006 at 1:51 am

    #616327

    Specify the column with unique index first so that the search will

    be easier..give : select ID from TBL with col1=@a..

    let me know if it works..

    ..sunder

  • January 23, 2006 at 2:06 am

    #616328

    i never heard about using with in a select query.

    what do you mean? can u explain?

    thx G

  • January 23, 2006 at 2:11 am

    #616331

    Sorry, it was 'where' not 'with'....

  • January 23, 2006 at 2:13 am

    #616332

    Sorry, it was 'where' not 'with'....

  • January 23, 2006 at 2:18 am

    #616335

    ahha

    as i wrote in my 2nd post, my query is the next:

    select ID from TBL where COL1= @a or COL2= @a

    i think, its almost the same, what u advice me, isnt it?

    my query's long run time couses by OR, i think.

  • January 23, 2006 at 2:53 am

    #616342

    I want to know what is happening when you make a composite index on the three columns.

    ..sunder

  • January 23, 2006 at 3:34 am

    #616353

    I want to know what is happening when you make a composite index on the three columns.

    What exactly do you want to know?

    select ID from TBL where COL1= @a or COL2= @a

    If you have an OR, SQL will only use indexes if there is an index on each of the columns involved in the OR. If you have an index on Col1 and another on Col2 then SQL will be able to use the indexes. Otherwise not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2006 at 4:32 am

    #616370

    thanks

    and what do u think? i have to create an index on ID, or not?

  • January 23, 2006 at 4:35 am

    #616373

    To be able to return results from the query, no you do not need to create an index. If you want the query to perform optimally you should however create two indexes, one on (Col1, ID) and the other on (Col2, ID). Do not just create indexes because this query would be faster with them though, you need to consider the system as a whole.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • January 23, 2006 at 4:57 am

    #616384

    Do you ever search on ID? Is it the PK of the table (and hence uniquely indexed)

    How many rows are in the table? How often is it queried? How often is it inserted, deleted or updated? Where is the clustered index?

    Don't create indexes just because you can. You should analyse the access patterns of a table to see if an index is necessary before adding one. Testing in a dev environment is also recomended.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2006 at 5:38 am

    #616396

    the ID is the PK! ... jesuschrist! i forgot it!

    so. this is that becouse i use ID to connect to another tables and for UPDATEs too. so ID is unchangeable.

    but COL1 must be uniqe too. so this is a nonclustered unique constraint. and COL1 is changeable. its almost like names.

    COL2 contains just a secondary information, like birth names. but i need for searching.

    the table has cca 20 000 rows.

    in the first time i have to query COL1 and COL2, but later i will use only the ID. in the first time, i make a big amount of INSERT in a very short time and some UPDATEs if its needed. later i wont. i dont make any delete or just a very few later.

Viewing 15 posts - 1 through 15 (of 21 total)

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