Database index help in speeding up the retrieval of data from tables. When we query data from a table the following process is followed:

1. -First MySQL checks if the indexes exist
2. -Indexes are used by MySql to select relevant rows of the table and do not scan the whole data.
A database index is very similar to an index page of a book. **For example:** If we want to find a topic in a book, we go to the index page, and then we open the exact page with exact topic without scanning the whole book. There are four general index types to consider when creating an appropriate index to optimize SQL queries.
1. - Column Index
2. - Concatenated Index
3. - Covering Index
4. - Partial Index

Syntax form creating , altering & droping index in MySql

CREATE INDEX index_name ON TableName(column name)    

ALTER TABLE TableName ADD INDEX (column name);

ALTER TABLE TableName DROP INDEX index_name;

Column Index:

In this type of indexing we have an index on a single column.

For example: If we want to query data on a column employee_id, we use the following query

ALTER TABLE Employee ADD INDEX (employee_id); 

table name = Employee coulumn name = employee_id

SELECT  employee_id, user_name FROM   Employee  WHERE employee_id = 1;

without an index the system reads all data and then sequentially scan the exact matching data. So we can optimized our query to only get the records that satisfy our criteria by adding an index to employee_id.

For more information on column index see the link

https://dev.mysql.com/doc/refman/5.5/en/column-indexes.html

---

Concatenated Index

In this type of indexing we have an index with multiple columns . A concatenated index uses multiple columns. We can use the following query to create a concatenated index :

ALTER TABLE Employee  ADD INDEX (employee_id, city_id);

SELECT employee_id, user_name FROM   Employee WHERE employee_id = 2  AND city_id = 3;

---

Covering Index

A covering index covers all columns in a query. The benefit of a covering index is that the lookup of the various Btree index pages necessary satisfies the query, and no additional data page lookups are necessary.

ALTER TABLE Employee  ADD INDEX (employee_id, user_name, city_id);

SELECT employee _id, user_name , city_id FROM Employee WHERE employee_id = 1;

---

Partial Index

It allows us to specify a subset of a column for the index. It is also known as filtered index which has some condition applied to it so that it includes a subset of rows in the table.

For example: Lets say we query data and allow pattern matching on last name.

SELECT employee_id, first_name, last_name, city_id FROM   Employee WHERE last_name like 'A%'

We add an index to last_name to improve performance.

ALTER TABLE Employee ADD INDEX (last_name);

Some more topics on indexes are as below:

In order to create indexes, we use the CREATE INDEX statement as below:

CREATE [ UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
USING [ BTREE | HASH | RTREE ] ON table_name (column_name [(length)] [ASC | DESC],...)

First, we specify the index based on the table type or storage engine:

UNIQUE indexes means MySQL will create a constraint that all values in the index must be unique. Duplicate NULL value is allowed in all storage engine except BDB.

Unique indexes work in much the same way as a primary key. Although we can only have one primary key, any number of unique indexes can be created with any number of fields.

In our example, we want to ensure no two records have the same city, phone number. We can do this by altering our table:

query:

 ALTER TABLE `Employee` ADD UNIQUE INDEX `phone_no` (`employee_id`, `first_name`,`phone_number`, `city_id`);

If we try inserting a duplicate record the below query will give an error.

INSERT INTO `Employee`(`employee_id`, `first_name`, `phone_number`, `city_id`)

---

FULLTEXT: this index only used in MyISAM storage engine and to implement we need column with data type CHAR, VARCHAR or TEXT.

FULLTEXT indexes are used for text searches using MATCH() / AGAINST() clause,

We can use FULLTEXT index in CREATE TABLE , or when we want to add index using ALTER TABLE or CREATE INDEX.

For more details on FULLTEXT see the link :

https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

---

SPATIAL index is used for MyISAM storage engine. Also, we can not put the column value NULL.

Keywords such as BTREE, HASH or RTREE are used on the basis of the storage engine that we have used to create our table.The list of storage engines and their corresponding allowed index types are as follows:

Storage Engine	Allowable Index Types
MyISAM	BTREE, RTREE
InnoDB	BTREE
MEMORY/HEAP	HASH, BTREE
NDB	HASH