Indexes are maintained in special tables that the database search engine can use for fast retrieval of data . An index in a database is very similar to an index in the back of a book. An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.
Single-Column Indexes:
A single-column index is created based on only one table column.
Create index indexname on tablename(columnname)
Unique Index:
A unique index does not allow duplicate values to be inserted into the table
Create UNIQUE INDEX indexname on table(columnname)
Composite Index:
A composite index is an index on one or more columns of a table.
Create INDEX indexname on tablename(column1,column2)
You have to decide whether you go for single column index or composite index based on the filter condition usage (i.e where clause)
Implicit index:
Index are automaticaly created for primary key constraints.
Avoid indexes on some places: