Re: What are common indexes?

Jim Kennedy wrote:

> In an OLTP application you don't want indexes on everything. Each index
> makes updates and inserts take longer. Usually an compound index is
> valuable when all or the "front" of the index is supplied. For example, to
> search for city,state then an index on city state would work. To search
> just for city then city state would still work. To search for state then
> city state would not be effective. (state, city would, but not in the first
> case) In the city state example an index on city and state is probably
> better than 2 seperate indexes, one one city and one on state. Since state
> will meet a lot of entries then a full table scan might actually be faster.
> (It depends.)
>

So if I frequently search on city and state separately as well as jointly then I would create 3 indexes? on city, on state and on (city, state)?

Also, the reason that we make an index on (city, state) rather than (state, city) is because we want to put the more discriminating index first?

>
> Jim
Received on Mon Aug 15 2005 - 09:44:22 CDT