MySQL Indexes

1. It is a DB object. Simply put, an index is a reference to data in a table. An index in a database is similar to an index in the back of a book. Indexes allow the database to find data fast, without reading the whole table. 

2. Indexes consume a physical space and grow up with the data of table and can often grow larger than the table.  Indexes should be created only on columns where lots of read operations are happening. But never create index for columns where lot of CUD (Create, Update, Delete) operations are happening. 

3. When a Primary Key or Unique Key is added on a column, that column is indexed implicitly. 

4. An index’s main purpose is to improve the performance of data retrieval. With index data retrieval is faster that full table scan does not happen and directly read the data by index reference. An index is faster because it typically stores information in an orderly treelike format. A full table scan occurs if there is no index on the table. 

Syntax to create indexes in MySQL

MySQL indexes example

Syntax to drop index