Indexing Fields

[DBFIN]

Indexing fields can dramatically improve efficiency by speeding up sorts and searchs. My database tracks mail sent on various mail dates to multiple customers with insurance policies that are assigned unique policy numbers. I've indexed the policy number.

Question 1: Is there value in indexing a mail date field which has duplicate values even for a given customer that has received multiple mailings?

Question2 : Should one index every field in every database ? Are there scenarios where one would choose not to index a fied ? If so, can someone provide an example ?

Thanks in advance for your assistance.

 

[KenHigg]

Indexing has its overhead and should never be done on every field just because it sounds good. The more rows and relationships you have the more import it is that you index only where absolutely required. Think of it this way: When you index you create what amounts to a new column in your table. This column has a value that establishes where the record is in the index. If you add a record, the index has to go through and figure out where to put the record in the index meaning it may have to go through all the records and change the value in the index column for each record. If you have ten fields and have an index on each field then you may have to go through this re-indexing ten times...

In a word, use indexing sparingly

 

[namliam]

Q1: If you use that field a lot to search/query on, sure use an index

Q2: NO, every index requires space. And space is not allways available... Index that what you need no more, no less.
What you need to index are fields that are:
- Key
- FK
- Searched often
Exceptions to that are rare, but do excist

 

[Rabbie]

Also remember that Numeric fields index more efficiently than Strings - taking up less space.

 

[DBFIN]

Thanks so much for everyones' input. The input was excellent and now I am fully equipped to appropriately utilize indexes.