What is indexing/indexes ?

What is indexing/indexes ?

Indexes is a data structure that improves the speed of operations in a table. Indexes can be created one or more columns.

It is used to speed up queries and will be used by database search engine to locate rewards very fast.
Insert and update statements takes more time on tables having indexes where as select statement become fast on those tables. The reason is that while doing insert or update database need to insert or update index value as well.

Types:

B-Tree Indexes:
  Normal Index : Contain duplicate value.
  Unique Index : can not add duplicate value but you can add null.
  Primary key : can not add null and duplicate value.
  Full-text indexes : Us for full-text searches. Search some specific keyword from the text-column.

B-tree index can be used for column comparison in expression like =,>,>=,<,<= or between oparator.

Spatial Indexes(R-Tree):
       Spatial indexes is supported by only MYISAM storage engine.

Hash Indexes:
       Hash index supported only by memory storage engine. Here we can use only equality comparison where we can use = or != operators.

syntax :
Unique index :
Create unique index index_name
ON table_name(col1,col2)

Create unique index Author_index
ON tutorials_tbl(tutorial_autohr)

Simple Index :
Create index index_name
ON table_name(col1,col2);

Display index:

Show Index from table_name;