Index, as this word itself described it is a way to make the process of locating a specific thing faster and easier in any book, library and of course in database. Particularly, indexing for any table of database is a way to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed. It is a data structure technique which is used to quickly locate and access the data in a database. Basically, an index contains a search key, sometimes performed as what we are familair with when we are using the CREATE TABLE function in mysql, we always have PRIMARY KEY as a unique value to correspond with data so that these data can be found quikly by locating the key. and another part is the data reference, wihch contains a set of pointers holding the address of the disk block where that particular key value can be found.
There are three methods of indexing, 1.Clustered Indexing 2. Non-Clustered or Secondary Indexing 3.Multilevel Indexing. Clustered indexing is used when we store more than two records in the same file. This kind of indexing can help the database to reduce the cost of searching reason being multiple records related to the same thing are stored at one place and it also gives the frequent joing of more than two tables. Clustered indexing is defined on an ordered data file and it is on a non-key field. Basically, we group two or more columns together to get the unique values and create index out of them is clustering index. Non-Clustered or Secondary Indexing just shows where the data lies, it gives us a list of virtual pointers or references to the location where the data is actually stored since data is not physically stored in the order of the index. It requires more time as compared to the clustered index because some amount of extra work is done in order to extract the data by further following the pointer. In the case of a clustered index, data is directly present in front of the index.Multilevel Indexing is to segragate the main data block into many small data blocks. This indexing method is used on solving the problem of a single-level index might become too large a size to store with multiple disk accesses. Particularly the outer blocks are divided into inner blocks, and these are pointed to the data blocks. This method makes data easily be stored in the main memory and it also reduced the cost.