Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: Indexes pros and cons

    I believe I have this question answered, can someone comment on it, perhaps suggest some more 'pros'?

    Indexes are sort of a bookmark within a table or a record, to mark a record, such as a primary key.

    Indexes allow the database application to find data fast; without reading the whole table. Somewhat like using a bookmark in a book.

    Indexes have the following disadvantages - they increase the disk space requirements of the database, they slow down dml (inserts, updates and deletes), they increase the maintenance needs of your database, they may make your queries slower instead of faster.

    Pros would be the ability to retrieve records faster

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Index can avoid accessing the table & satisfy the SELECT, which provides result set faster.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    Index can avoid accessing the table & satisfy the SELECT, which provides result set faster.
    Thanks, I'll go with that. Makes sense, really.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Don't forget that indexes are optimized for searching and will speed up any search quite a bit and it isn't just big tables. I had a company table with 5 entries in it that was unindexed and heavily linked. I threw a primary key on it and the queries ran 4 time faster because of the search optimization on the index. I will acknowledge that that was in an Oracle 7 database.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Indexes are used to enforce unique and primary key constraints.

    Indexes help reduce lock contention, e.g. during the child table update where a foreign key exists.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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