Indexes in SQL

SQL Indexes

Indexes are used in fetching records from the database more quickly. It improves query performance by applying indexes on fields. Hence the retrieval of values on fields becomes very fast. Index generally boost your query performance with  SELECT queries and WHERE clauses but it give an impact on INSERT and UPDATE statements as it slows down the  data manipulation operations

Limitation of applying index on a table :

Index applied tables take more time to compare to the table with without index

Single Column Indexes

Create Index: It applies an index on a table with duplicate values in the field

Syntax :

CREATE INDEX <index name>
ON <table name> (<column1>);

Let’s understand how to apply single-column index in SQL with an example,

Single column Index

In the above example, we have created an index named “Student_Index” on the “id” field of the “student” table. As we have passed only a single field “id”, it will create an index on single column of the “student” table. This index can be used

& fetch all the duplicate entries of “id” column in “student” table

Unique Indexes

Create Unique Index: It applies the index on a table without duplicate values in the field

Syntax :

CREATE UNIQUE INDEX <index name>
ON <table name> (<column1>);

Let’s understand how to apply unique index in SQL with an example,

Unique Index

In the above example, we have created an index named “Student_Index” on the “id” field of the “student” table. As we have passed only a single field “id”, it will create an index on a single column of the “student” table. This index can be used & fetch only unique entries of “id” column in “student” table

Composite Indexes

Create a Composite Index: It applies indexes on two or multiple columns of the table.

Syntax :

CREATE INDEX <index name>
ON <table name> (<column1>, <column2>, …);

Let’s understand how to apply the composite index in SQL with an example,

Composite Index

In the above example, we have created an index named “Course_Index” on the “id” field of the “Course Schedule” table. As we have passed two fields  “sid” & “id” , it will create an index on two columns of the “student” table. This index can be used & fetch all  entries of “id” & “sid” columns in “Course_Index” table

Implicit Indexes

These indexes are automatically created by DBMS system whenever an object is created with primary & unique constraints Indexes can be removed from tables, Once we remove the index from tables there will not be any impact on records of the table

Drop Index: It removes index from the table

Syntax :

DROP INDEX table_name.index_name;

Let’s understand how to apply unique index in SQL with an example,

Implicit Index

In the above example, we are dropping an index named “student_index”. Once we drop the index from the table, there will not be any impact on the data values of fields in the table.

Where not to use index :

  • Avoid applying indexes on small tables
  • Avoid using indexes on transactional tables where bulk insert & update operations are scheduled
  • Indexes should not be applied on a column containing higher % of null values
  • Fields which are frequently changed should not be indexed

Related BLogs:

Leave a Reply

Your email address will not be published. Required fields are marked *

Looking for Online Training