What are Database Indexes?

An article by Jorge Reis    Comments Off

The database index is defined as a database structure whose primary function is to make the operations on a database table faster. Creating a database index requires one or a number of columns on a table for faster accessing of records in a database.

Database indexes only occupy a small part of the disk memory since they only contain important key fields and discard the other fields within the table. This way, the index can be stored even though the database holds a large number of data. Index can also be defined as either non-unique or unique, where unique index functions as a table constraint where it stops replication of rows in an index.

Database Indexes

When it comes to relational databases, the index is considered to be a part of table of the database. The power of the database index is sometimes extended by the other types of databases through creating them within functions or expressions in coding. An example of this is upper (first_name) where the index will only store entries whose first name is in the upper case. Filtered index is another option that is used in databases. The function of the filtered index is to include the data if and only if it passes a criteria designated by the administrator.

Simply put, the index sorts data using key fields or values. Another description of a database index is that it refers to a key value in the right field of the table and is capable of retrieving data in a fast way as long as you know the right search arguments.

Using Database Indexes

To make the most out of the database index here are some tips that you can follow. First is to use a unique element in the index first. Make sure that the element that you will be using has the highest or largest value. This will help you find the page that you are looking for in a faster way. Make sure that the index that you will be creating is small. This will enable you to find pages faster, unlike having a large index. Large indexes can be used for those whose functions are of frequent use. For those who are using small tables, it is better for you not to use indexes as this will only slow down the searching process. It is better to allow the database search all of its record, rather than using indexes. Take note that database index slows down any operations done to the database. This includes deleting or adding data in the database. This is because, once you alter a record in the database, the index also needs to be updated. The index that you will be creating should be a key value that is not updated all the time. This will help your database retrieve data faster.

Types of Database Indexes

There are four types of database index, and these are bitmap index, dense index, sparse index and covering index. In bitmap index, most of the data is stored by bulk in bitmap format. Dense index is a file containing a pair of key and pointer for each record it has in a file. Sparse index is almost the same as the dense index, but it uses keys and pointers for each block in a data file.