Is one multi-key index faster than having lots of single-key indexes

Hi Guys/Gals,

I'm trying to improve the performance of our MS SQL 2000 database.

I've got a table with 5 int colums(among other things) and most SP's query data based on all five.

After some initial tests, my results are somewhat inconsistent/inconclusive.

I'd like to ask someone who's been doing this for quite some time.

Is it better to have one multi-key index(5 ints) or have a separate index for each individual column?

Thank you in advance,
Vlad OrlovskyI believe it depends upon the version of SQL Server, but I am pretty confidant that SQL 2000 for sure will use multiple single segment keys when available. (though this would likely add some overhead over a single index). As always, I would confirm with your data and testing (showing execution plan in Query Analyzer). If you ever use the columns individually (and not always starting with the one that is leftmost) then you would want the individual indexes.|||The SQL Server query engine is going to pick the best index and use it. It won't use multiple indexes for a single WHERE clause. So it will only use one of your indexes. If you are normally querying with all 5 columns in your WHERE clause, you will typically get better performance if you include all 5 columns in a single index. As always, it depends on the data, number of rows, the cardinality of the data, etc. to determine how much difference this will make in performance. But generally SQL Server doesn't have to work as hard if the WHERE clause is covered in a single index.

cs|||Books online for SQL 2000 says it will.

"Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query. For example, given the sample query:
SELECT * FROM lineitem
WHERE partkey BETWEEN 17000 AND 17100 AND
shipdate BETWEEN '1/1/1994' AND '1/31/1994"

SQL Server can exploit indexes on both the partkey and shipdate columns, and then perform a hash match between the two subsets to obtain the index intersection."|||really? that's not been my experience in the real world. not sure what conditions must be met for this to happen. i've just been doing quite a bit of query tuning and based on the execution plan, I have rarely seen SQL Server actually use more than one index. but thanks for tracking that down Douglas, good to know what the BOL says.

i'm not sure the multiple alias thing is quite the same animal anyway. just sharing what I've seen based on personal experience. as always, it's worth trying different approaches to see what performs best in a particular case.

cs|||Thanks for your tips guys. "Execution plan" thing is a very useful feature.
I did some testing on 93K+ records. And it turns out that having one single multi-key index is better than multiple indexes when selecting all keys in the WHERE clause.

Once I got my multi-key index to sort in the right order, time spent on final "Sorting" of the data has disappeared(that was something like .0183 I/O Cost).

Sincerely,
Vlad Orlovsky|||Yes, sorting can be an issue when using multiple keys.