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.
08-26-2002, 09:32 PM
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.