DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: index

  1. #1
    Join Date
    Oct 2000
    Posts
    90

    Angry

    I have an application that uses composite index on 3 columns(col1,col2,col3) to do a search.
    Now I have a new application which would use only col2 to do a search.
    What I would want to do now is to create a single column index on col2 for the new application to use.

    Would this be a problem? Is this approach a reasonble approach?




  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If you use both indexes for your queries, sure, its reasonable.
    Jeff Hunter

  3. #3
    Join Date
    Jun 2000
    Posts
    117

    Red face

    minor slow down for inserts because of multi idexes on a table.

  4. #4
    Join Date
    Oct 2001
    Posts
    83
    Hello,

    What about redefining the indexe in order to have col2 in the first position and so, if you use a query with WHERE condition having col1, col2 and col3, you will use the index. if you use only col2 in WHERE clause, you will use the same indexe.


  5. #5
    Join Date
    Oct 2000
    Posts
    90
    Sorry. It was my mistake.
    The second application will use col1 for the query instead of
    col2 like I indicated.

    The query would look like this:
    select A,B, from table_name were col1 .........

    So since the second application would be using col1,
    I am assuming I don't have to create any new index
    on col1 since the composite index already on
    (col1,col2,col3).

    Am I right?

  6. #6
    Join Date
    Oct 2001
    Posts
    83
    Hello,

    Yes you are right. Col1 is the leading column in your composite index so this last will be used if you use only col1 in the WHERE clause (obviously, you must not have a WHERE clause like : col1 not like '%xx' for instance --> index not used)...

    Hope this helps

  7. #7
    Join Date
    Oct 2001
    Location
    Pelham, AL
    Posts
    40

    Thumbs up

    You are correct, both queries can use the same index.

  8. #8
    Join Date
    Oct 2000
    Posts
    90
    Thanks

Posting Permissions

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


Click Here to Expand Forum to Full Width