Programming2Learn.com

Database Indexing tutorial

Written By: - Nov• 06•12

database index tutorial

Database Indexing Tutorial

Indexing is very important for the best utilization of a database. It is quite important to optimize a database for fast and efficient processing. Slow information processing results in user frustration and delays in work outputs. Database indexing is a process to optimize database tables for fast processing so that user requests for data retrieval may be entertained spontaneously. We hope you find the title database indexing tutorial helpful in your quest in learning about databases.

How Indexing Works?

The Database index is a data structure that is associated with one of more columns of a database table. Database indexing provides a fast path access to the database records. Indexed field is sorted with respect to some criteria which helps to reach the requested record much quickly. An indexed field holds a pointer for each record which points the the next record in the sequence. For example, if records in Students table are indexed by first name then an index along with a criteria, lets say alphabetical order, is attached to this field and database management system performs table queries by utilizing this index.

Usage of Indexes

Indexing can either be applied to one column of the table or more than one column. The exact number of indexed columns depend on the specific development scenario. Although utilization of indexing speeds up data access, its use on many table columns is not encouraged as it has its own drawbacks. The more are indexed columns, the more disk storage will be utilized because there is a pointer associated with each data cell which points to the next record in the sequence. Moreover, it is a resource consuming process as well because DBMS has to update the index on each column at every insertion, updating and deletion of records in the table. So, it puts an extra burden on the DBMS for  each data manipulation activity.

An Example

We can have a look at the better utilization of indexing with an example. Suppose you have a table with 50,000 records. If you don’t have indexing on any column of the table, you will have to follow a linear search process and you will have to traverse the whole table in search of your required data, until you find it. You will even have to traverse all the records of the table if this table allows duplicates because you may hope for finding your required record at any place of the database. But indexing forces the searching of data with a specific criteria and gets to the required record with only a few traversal steps rather than traversing all the 50,000 records.

Types of Indexes

There are two major types of indexes i.e. clustered or non-clustered.

Clustered Indexes

Clustered index is used to sort the whole table physically. One table cannot have more than one clustered index. It is implemented in B-tree structure and records are held in the leaf level of B-tree.

 

Non-clustered Indexes

Non-clustered index is a type of index which logically reorders table data and this logical order may be different than the physical order. Non-clustered index contains pointers which point to the physical records in the table.

Optimizing your database tables is very important and you must never undermine its importance as a database developer. Fast and efficient data retrieval ensures faster information processing, resulting in much better business outputs.

 

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply