Indexes in databases

vijay's Avatar, Join Date: Jan 2006
Newbie Member
Why indexes were used in databases?
What is the exact use of indexes?
0
pradeep's Avatar, Join Date: Apr 2005
Team Leader
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
0
coderzone's Avatar, Join Date: Jul 2004
Team Leader
Quote:
Why indexes were used in databases?
To speed up the browsing and searching through the data of the database.
Quote:
What is the exact use of indexes?
To speed the data retrieval.
0
vijay's Avatar, Join Date: Jan 2006
Newbie Member
Thanks a lot pradeep!!

Thanks for replying me.
0
pradeep's Avatar, Join Date: Apr 2005
Team Leader
The pleasure is all mine.
sundeep.kumar like this