1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Indexes in databases

Discussion in 'Database' started by vijay, Jan 30, 2006.

  1. vijay

    vijay New Member

    Joined:
    Jan 30, 2006
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Why indexes were used in databases?
    What is the exact use of indexes?
     
  2. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    An index is a feature in a database that allows quick access to the rows in a table. The index is created using one or more columns of the table. Not only is the index smaller than the original table (due to having fewer columns), but it is optimized for quick searching, usually via a balanced tree.

    Some databases extend the power of indexes even further by allowing indexes to be created on functions or expressions. For example, an index could be created on upper(last_name), which would only store the uppercase versions of the last_name field in the index. Indexes can also be defined as unique or non-unique. A unique index acts as a constraint on the table by preventing identical rows in the index and thus, the original columns.


    Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.

    Indexes are useful for many applications but do come with some limitations. Consider the following SQL statement: SELECT first_name FROM people WHERE last_name = 'Pradeep';. To process this statement without an index the database software must look at the last_name column on every row in the database (this is known as a full table scan). With an index the database simply follows the b-tree data structure until the Pradeep entry has been found; this is much less computationally expensive than a full table scan.

    You can read more about indexing at http://www.dbmsmag.com/9605d15.html
     
  3. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    To speed up the browsing and searching through the data of the database.
    To speed the data retrieval.
     
  4. vijay

    vijay New Member

    Joined:
    Jan 30, 2006
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Thanks a lot pradeep!! :)

    Thanks for replying me.
     
  5. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    The pleasure is all mine.
     

Share This Page