Database Indexing and How Indexes Work in MySql.

What is a Database Index?

• Database Indexing is a process or way to optimize and retrieve the query records from the DB faster.
• In general, whenever we query Database or Whenever Database process the query, disk Access is required. Query processing takes time if Indexing is not done, if the data is in large chunk.  
• Indexing is a Data structure method or technique which uses to query or locate the data faster from the database table by minimizing the usage of disk Access.
• Index is table which have only Two Column. First Column which always consist of Primary Key or Candidate Key.

Type of Database Indexes

1. Primary Indexing :

• By Default most of the table what we create in DB follow primary indexing
• Primary index uses primary key of the table which is unique to each record of the table and in the sorted order.
• Primary Key based indexing follow 1:1 relation between the records.

2. Clustering Index:

• Clustered Indexing is key value based index which helps in sorting the table record.
•   Per table only one Clustered Index can be created.
• In RDBMS, Primary key is used to create the Clustered Index based on specific column. 

For Example:  Suppose you created One Table named with Student_data and There Roll number is primary key in the table, then Clustered Index will be created automatically.  

3. Non-Clustered Index:

• Non-Clustered Index is Faster than Clustered Index. In single table you can create one or more Non-clustered index.
• Non-Clustered Index is an index structure separate from the table structure which uses one or more table-column for structuring.

How to Create Database Index in MySql 

Step 1 :Create Database with Name as student. (Follow the same code Written Below)

CREATE DATABASE student;

---

Step 2: Create Table Name as student_info and Insert Data in student_info 

CREATE TABLE student_info (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(90) DEFAULT NULL,
  `dob` DATE DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `student_info` (`name`, `dob`) VALUES('Kumar','2003-04-07');
INSERT INTO `student_info` (`name`, `dob`) VALUES('ISHAQ','2002-06-14');
INSERT INTO `student_info` (`name`, `dob`) VALUES('ANBUSELVAM','2004-06-18');
INSERT INTO `student_info` (`name`, `dob`) VALUES('MARTIN','2004-03-08');
INSERT INTO `student_info` (`name`, `dob`) VALUES('JAGAN','2003-04-10');

MySql Command to Create Index
The Syntax for creating Index is given Below

CREATE INDEX IndexName ON TableName;

Single-Column Index

In Single Column Index we use only one column of table for creating Index. Syntax is given below.

CREATE INDEX IndexName ON TableName (ColumnName);

Unique Indexes

Unique Indexing ensure the Data Integrity as well as enhance the Query Execution speed because Unique Index do not allow duplicate data to be inserted. Syntax is given below :

CREATE UNIQUE INDEX IndexName ON TableName(ColumnName);

Composite Indexes

We Create Composite Index using multiple column of the table. Syntax to create the Composite Index is below:

CREATE INDEX IndexName ON TableName (ColumnName1, ColumnName2);

Note: Whatever column you are using for creating Index (Either Composite or Single-Column Indexing) from table, Use only those column which is used frequently with WHERE Clause.    

Implicit Indexes

Implicit Index is default index created once the table is created. Implicit Indexing is created on Primary Key Constraints or Unique Key Constraints.

Note:-  When to use Indexing and when Not to use.

• Avoid Using or creating Index on small Table.
• We Must Not create index on those Table  column which have more NULL Values.
• Don’t Create Index on those table column which get manipulate frequently.

Database Index Option Type is of Four Type :

• FullText Type
• Unique Type Index
• Primary
• Key

Fulltext Type : While you create Index on table it will ask you the Index type or Index Choice, Where you will find FULLTEXT Type is as one option. FULLTEXT is one of the option in full-text Index. We can Select FULLTEXT option only for VARCHR, CHAR or TEXT Column type.

Unique Type Index:  While you select unique type index from the option you must select only those table column which have unique value.

Key Type Index: It is Normal Indexing type where you can select multiple Table column for the indexing purpose.

Primary Type: This indexing we create on primary key. As we know per table only one primary key is available in sorted order which help us in fetching row efficiently.

Database Indexing is one of the most frequently asked interview questions on DB Index. As a Developer we must know How indexing works in mysql, oracle etc which help us to optimize our query for the faster retrieval of data from the database.

What is a Database Index?

• Database Indexing is a process or way to optimize and retrieve the query records from the DB faster.
• In general, whenever we query Database or Whenever Database process the query, disk Access is required. Query processing takes time if Indexing is not done, if the data is in large chunk.  
• Indexing is a Data structure method or technique which uses to query or locate the data faster from the database table by minimizing the usage of disk Access.
• Index is table which have only Two Column. First Column which always consist of Primary Key or Candidate Key.

Type of Database Indexes

1. Primary Indexing :

• By Default most of the table what we create in DB follow primary indexing
• Primary index uses primary key of the table which is unique to each record of the table and in the sorted order.
• Primary Key based indexing follow 1:1 relation between the records.

2. Clustering Index:

• Clustered Indexing is key value based index which helps in sorting the table record.
•   Per table only one Clustered Index can be created.
• In RDBMS, Primary key is used to create the Clustered Index based on specific column. 

For Example:  Suppose you created One Table named with Student_data and There Roll number is primary key in the table, then Clustered Index will be created automatically.  

3. Non-Clustered Index:

• Non-Clustered Index is Faster than Clustered Index. In single table you can create one or more Non-clustered index.
• Non-Clustered Index is an index structure separate from the table structure which uses one or more table-column for structuring.

How to Create Database Index in MySql 

Step 1 :Create Database with Name as student. (Follow the same code Written Below)

CREATE DATABASE student;

---

Step 2: Create Table Name as student_info and Insert Data in student_info 

CREATE TABLE student_info (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(90) DEFAULT NULL,
  `dob` DATE DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `student_info` (`name`, `dob`) VALUES('Kumar','2003-04-07');
INSERT INTO `student_info` (`name`, `dob`) VALUES('ISHAQ','2002-06-14');
INSERT INTO `student_info` (`name`, `dob`) VALUES('ANBUSELVAM','2004-06-18');
INSERT INTO `student_info` (`name`, `dob`) VALUES('MARTIN','2004-03-08');
INSERT INTO `student_info` (`name`, `dob`) VALUES('JAGAN','2003-04-10');

MySql Command to Create Index
The Syntax for creating Index is given Below

CREATE INDEX IndexName ON TableName;

Single-Column Index

In Single Column Index we use only one column of table for creating Index. Syntax is given below.

CREATE INDEX IndexName ON TableName (ColumnName);

Unique Indexes

Unique Indexing ensure the Data Integrity as well as enhance the Query Execution speed because Unique Index do not allow duplicate data to be inserted. Syntax is given below :

CREATE UNIQUE INDEX IndexName ON TableName(ColumnName);

Composite Indexes

We Create Composite Index using multiple column of the table. Syntax to create the Composite Index is below:

CREATE INDEX IndexName ON TableName (ColumnName1, ColumnName2);

Note: Whatever column you are using for creating Index (Either Composite or Single-Column Indexing) from table, Use only those column which is used frequently with WHERE Clause.    

Implicit Indexes

Implicit Index is default index created once the table is created. Implicit Indexing is created on Primary Key Constraints or Unique Key Constraints.

Note:-  When to use Indexing and when Not to use.

• Avoid Using or creating Index on small Table.
• We Must Not create index on those Table  column which have more NULL Values.
• Don’t Create Index on those table column which get manipulate frequently.

Database Index Option Type is of Four Type :

• FullText Type
• Unique Type Index
• Primary
• Key

Fulltext Type : While you create Index on table it will ask you the Index type or Index Choice, Where you will find FULLTEXT Type is as one option. FULLTEXT is one of the option in full-text Index. We can Select FULLTEXT option only for VARCHR, CHAR or TEXT Column type.

Unique Type Index:  While you select unique type index from the option you must select only those table column which have unique value.

Key Type Index: It is Normal Indexing type where you can select multiple Table column for the indexing purpose.

Primary Type: This indexing we create on primary key. As we know per table only one primary key is available in sorted order which help us in fetching row efficiently.

Database Indexing is one of the most frequently asked interview questions on DB Index. As a Developer we must know How indexing works in mysql, oracle etc which help us to optimize our query for the faster retrieval of data from the database.