What is Indexing in Database ?
- Indexing is the One of the ways that will optimize the database searching and accessing is having indexes on the columns that access the data in a database table.
- An index object is created in database with the column/columns value that are mentioned while creating the index.
- The value in the index holds the row id of the physical data location in the database which helps for fast data retrieval of the corresponding rows.
Types Of Index
- Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
- Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
Cluster & Non Clustered Index
Advantages of Indexes
- Indexes their use in queries usually results in much better performance.
- Make it possible to quickly retrieve (fetch) data.
- They can be used for sorting. A post-fetch-sort operation can be eliminated.
- Unique indexes guarantee uniquely identifiable records in the database.
Disadvantages of Indexes
- They decrease performance on inserts, updates, and deletes.
- It will take up space (this increases with the number of fields used and the length of the fields).
- Some databases will monocase values in fields that are indexed.