Key factors for defining indexes.

Hello,
Plz explain me what are the key factors or conditions one should consider while defining indexes on a field.
Regards,
ShaileshYou need to balance the time spent buidling and maintaining the index(es) versus the time that they will save you on queries. As a side effect, you also need to consider how much disk the index will use too.

Every time you change an indexed column, you'll need to update every index that references that column. This slows down every INSERT and DELETE statement, and many UPDATE statements too.

Each index serves somewhat like a "stored sort" for the data in the table. The database engine can refer to the index pages in order, and find the related data rows relatively quickly and easily. As an example, you could create an index on last name and first name, insert the information from a telephone book in random order, but still retrieve them in the order of the index without having to do another sort.

The final decision comes down to performance nearly every time. If you can take the performance hit during database changes, and get a perfromance benefit when you retrieve the data, then the index is worthwhile.

-PatP