Re: multiple indexes or a composite index?

As Sybrand sez:
>>Some general guidelines on indexes:
>>1 For all composite indexes: the most discriminating column must be leading

I have found many badly designed composit indexes improve performance by a factor of 10 when rebuilt with the first attribute having the best domain (number of values and even distribution).

This is because it works better for other queries that just might use that attribute. If the "best" attribute is not at the front of any index its chances of being used is much less as it has to match exactly the attributes used in the query where clause to the depth it is buried in the index.

Too many indexes are designed for one query by an inexperienced developer with a single-application-centric view of how a database is used.

-- 
---------------------------------------------------------
Steven Hauser 
email: hause011@tc.umn.edu   URL: http://www.tc.umn.edu/~hause011
---------------------------------------------------------