Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    7

    Cool Unanswered: 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,
    Shailesh

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •