What is Index? What are the advantages and disadvantages?

• Index is a physical structure contains pointers to the data.
• The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application.

Two type 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.

When to Create an Index:
The advantages of indexes are as follows:

• Their use in queries usually results in much better performance.
• They 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.

The disadvantages of indexes are as follows:

• They decrease performance on inserts, updates, and deletes.
• They 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.

You should only create indexes when they are actually needed.
Take care not to add an index on something that has already been indexed. If you need a more detailed index, you can add fields to an existing index as long as it is not a unique index.