1 mult. column vs mult. 1 column indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: 1 mult. column vs mult. 1 column indexes

  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Question

    In general, is it better from a query performance perspective (I'm not particularly concerned about disk space) to have one index on multiple columns, or to create individual indexes for each column needed in queries?

    My specific issue is with a table of appx. 100K rows and 15 columns. 9 of those columns I think should have an index on them as they'll be used for various queries (usually no more than 2 or 3 at a time though).

    I'm concerned that if I make one or two multi-column indexes, many times they won't be used for the query. However, having a 15 column table with 9 indexes also seems rather odd.

    I'm more interested in a "general" discussion and not necessarily specific to my current concern. This is an extreme case but I've pondered this on other tables in the past.

    Thanx,
    GaryG

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    First of all, if you are talking about unique indexes (unique/primary) there's no discussion, it's different two unique-indexed columns than two indexed columns ... unique-indexed ones cannot exist twice as a group.

    You create an index with multiple columns if these columns are used in queries very often together. If the columns are queried alone, there's no need for creating a index with multiple columns. And besides, the columns may have different properties ... one columns is good with bitmap, other with a reverse index, and so on ... but in general, if the columns are queried in group very often, create an index with multiple columns.

    F.

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