Tanubhrt.This.Side

Indexes form the basic structure of retrieving data via the database. Although when the data is small, having an index does not makes much difference , but with the increase in the data, the importance of having the right indexes becomes quite obvious. This is because in case of an indexed file/table, a new table is maintained which maintains the index and the pointers. Having an extra table with say 20 records, does not makes much sense. But for lacs, an index ensures better search performance. In this series of post, we will try to understand what indexes are!

In the words of Navathe, Indexes are additional auxillary access structures which are used to speed up the retrieval of records in response to certain search conditions. The index structures typically provide secondary access paths, which provide alternative ways to access the records without affecting the physical placement of records on the disk. They enable efficient access to records based on the indexing fields that are used to construct the index. Any field of the file can be used to create index and multiple indexes can be constructed on the same file.

In simpler terms, the way indexes work for a file is –

1. You define the criteria on which you want an index to be created.
2. When you search the file on the basis of an index, the index is searched for
3. This index has a pointer which points to the matching records or the block where the records are kept.

Indexes can be classified on the basis of ordered files which are the single level indexes or tree data structures which are the multilevel indexes and B+ trees. We can even create indexes based on hashing or other search data structures.

TYPES OF INDEXES

1. Single Ordered Indexes
   1. Primary Indexes
   2. Clustering Indexes
   3. Secondary Indexes
2. Multi-level Indexes
3. Dynamic Multi-level indexes

We will try to understand these in detail in upcoming post.

Happy Learning 🙂