About MySQL Index

An index is also essentially a table that stores the column item values that are listed as indexes and pointers to true full records. The index is transparent to the user and is used only by the database engine to speed up the retrieval of real records. Indexed tables, insert and update operations take more time and select is faster because insert and update operations also insert and update index values, but these operations are also transparent to the user. An index is typically used in a where, join, order BY statement [1].

In MySQL, index and key are synonyms, and both are interchangeable [2]. A table can have up to 16 indexes, and each index consists of up to 15 columns. There are 4 types of indexes in MySQL. The index is stored differently [3] depending on the table type (and also the storage engine).

• Primary key can specify that one or more columns be combined into an index to uniquely determine a record, and each item specified as primary key must be unique and cannot be empty. A table has at most one primary key. Table with primary key set automatically as primary key
  Build the index.
• Unique key similar to primary key, you can specify one or more columns to be indexed to uniquely identify a record. Each item specified as a unique key must be unique, allowing null values. A single table can have multiple unique keys.
• The fulltext type index can only be used for MyISAM tables, and only for char,varchar,text column items.
• Normal index, there is no uniqueness limit, there is no non-null limit.

Storage Engine	possible types of indexes
MyISAM	Btree
InnoDB	Btree
Memory/heap	Hash,btree
NDB	Btree,hash

The index can be created when the table is created:

...] );

Where create_column_definition can be replaced by:

• column_name column_definetion
• [constraint [symbol]] primary key (column_name, ...) [index_type]
• [constraint [symbol]] unique [index|key] (column_name, ...) [index_type]
• {index|key} [index_name] (column_name, ...) [index_type]
• {fulltext} [index | key] (column_name, ...) [index_type]
  Where column_definetion can be replaced by:
• data_type [not null | null] [default default_value]
• [auto_increment] [unique [key] | [primary] key]
• [comment ‘string‘] [reference_definition]

For example:

create  table  test ( ' id '   int  unsigned not  null  auto_increment,< Span class= "hljs-string" > ' data0 '  varchar  (20 ),  varchar  ( 20 ), primary  key  ();   

create table test(id int unsigned not null auto_increment primary key,`data0` varchar(20),`data1` varchar(20));

You can also add an index after the table is created:
1) Use the ALTER command:

... ];

Where alter_sepcification can be replaced by either:

• add [constraint [symbol]] primary key (index_cloumn_name, ... ) [index_type]
• add [constraint [symbol]] unique [index|key] [index_name] (index_cloumn_name, ... ) [index_type]
• add {index | key} [index_name] (index_cloumn_name, ... ) [index_type]
• add [fulltext] [index|key] [index_name] (index_cloumn_name, ... ) [index_type]

Where index_cloumn_name can be replaced by: column_name [(length) [Asc|desc]]
Where index_type can replace: using {Btree|hash}
For example:

alter table test add unique key `index_data0` (`data0` (10));

2) Use the Create command:

... ) [index_type];

Where index_cloumn_name can be replaced by: column_name [(length) [Asc|desc]]
Where index_type can replace: using {Btree|hash}
A few things to note:

• The Create command cannot be used for creating primary key
• The ability to set index prefix lengths for char,varchar,binary,varbinary, which means that you can index only a portion of these fields earlier.
• If the blob and text are index entry types, you must specify the index prefix length [5].

For example:

create index `index_data1` on test (`data1` (10));

To delete an index:

alter table table_name drop primary key;alter table table_name drop {index | key} index_name;

When you create a multi-column index, you can use the index [6] to query the first n columns of all indexes or indexes (consistent with defining the index order). For example:

create table test(id int not null auto_increment,last_name char(30) not null,first_name char(30) not null,primary key (id),index name (last_name, first_name));

The following queries can use the index name:

 Select *  from Test where last =' Xyb '; Select *  from Test where last =' xyb '  and First_name= ' Love '; Select *  from Test where last =' xyb '  and (first_name=' Love ' or first_name=' Charlotte '); Select *  from Test where last =' xyb '  and First_ Name >= ' l '  and first_name <= ' n ';

The following Chauncey cannot be used with the index name:

select * from test where first_name=‘Charlotte‘;select * from test where last=‘xyb‘ or first_name=‘Charlotte‘;

A comprehensive explanation of the index can be found in the link [7]

Reference Links:
[1]http://www.tutorialspoint.com/mysql/mysql-indexes.htm
[2]http://stackoverflow.com/questions/3844899/difference-between-key-primary-key-unique-key-and-index-in-mysql
[3]https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
[4]https://dev.mysql.com/doc/refman/5.0/en/alter-table.html
[5]https://dev.mysql.com/doc/refman/5.0/en/create-table.html
[6]https://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
[7]http://blog.csdn.net/tianmohust/article/details/7930482

About MySQL Index