Indexing In SQL

Indexing in (DBMS)database is a optimization technique which is used to improve the performance of query operations, such as SELECT, UPDATE, DELETE. Indexes are used to retrieve data from the database quickly. Users cannot see the indexes, they are just used to speed up searches and queries. Indexes provide a quick way to look up records in a database table based on the values in one or more columns.

Indexing in SQL is a crucial technique for optimizing database performance by allowing the database management system to quickly locate and retrieve data based on specific columns. It is a balance between improved query performance and increased overhead during data modification operations.

Types of Indexing

1. Primary
2. Clustering
3. Secondary

• Primary Indexing:- In primary indexing, there is a one-to-one relationship between the entries in the index table and the records in the main table. Primary index can be of two types:(a)Dense primary index: The number of entries in the index table is the same as the number of entries in the main table. In other words, each and every record in the main table has an entry in the index.(b)Sparse or Non-Dense Primary Index: For large tables the Dense Primary Index itself begins to grow in size. To keep the size of the index smaller, instead of pointing to each and every record in the main table, the index points to the records in the main table in a gap.
• Clustering Indexing:-It may happen sometimes that we are asked to create an index on a non-unique key. Clustered index in SQL is a specific type of index that determines the physical order of data in a table. It is commonly used for the primary key of a table and can significantly improve query performance for operations that involve the indexed column.
• Secondary indexing:- Secondary indexing is used to improve the performance of queries by creating additional indexes on columns other than the primary key. While the primary key index ensures the uniqueness of rows in a table, secondary indexes provide faster access to specific columns or combinations of columns, allowing for quicker retrieval of data based on the indexed columns.