Mysql index Overview

Mysql index Overview

An index is essentially a type of table. This type of table stores the column values listed as indexes and pointers pointing to real complete records. The index is transparent to users and is only used by the database engine to accelerate the retrieval of real records. For indexed tables, the insert and update operations will take more time, while the select statement will become faster, because the insert and update operations also require both the insert and update index values, however, these operations are transparent to users. Indexes are usually used in the where, join, and order by statements [1].

In mysql, index and key are synonyms, and they can be exchanged [2]. A table can have up to 16 indexes, and each index can contain up to 15 columns. Mysql has four types of indexes. Indexes are stored in different ways based on the table type (or storage engine) [3].

• Primary key can be used to specify one or more columns as indexes to uniquely identify a record. Each item specified as primary key must be unique and cannot be blank. A table has at most one primary key. The table with the primary key set is automatically set as the primary key.
  Create an index.
• The unique key is similar to the primary key. One or more columns can be combined into an index to uniquely identify a record. Each item specified as the unique key must be unique and a NULL value is allowed. A table can have multiple unique keys.
• Fulltext indexes can be used only for MyISAM tables, and only for char, varchar, and text columns.
• Normal indexes have no uniqueness restrictions and are not empty restrictions.

Storage Engine	Possible index types
MyISAM	Btree
InnoDB	Btree
Memory/Heap	Hash, btree
NDB	Btree, hash

You can create an index when creating a table:

create table table_name (create_column_definition [, ...] );

Create_column_definition can be replaced:

• 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]
  Column_definetion can be replaced:
• 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,`data0` varchar(20),`data1` varchar(20),primary key (`id`),);

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

You can also add indexes after creating a table:
1) run the alter command:

alter table table_name[alter_specification [, alter_specification] ... ];

Alter_sepcification can be replaced with any of the following:

• 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]

Here, index_cloumn_name can be replaced with: column_name [(length) [asc | desc]
Here, index_type can be replaced with: using {btree | hash}
For example:

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

2) use the create command:

create [unique|fulltext|spatial] index index_nameon table_name (index_cloumn_name, ... ) [index_type];

Here, index_cloumn_name can be replaced with: column_name [(length) [asc | desc]
Here, index_type can be replaced with: using {btree | hash}
Notes:

• The create command cannot be used to create a primary key.
• You can set the index prefix length for char, varchar, binary, and varbinary, which means you can only index the first part of these fields.
• If blob and text are of the index type, you must specify the index prefix length [5].

For example:

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

Delete An index:

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

After creating multiple index columns, you can use the index [6] to query the first n columns of all or indexes (consistent with the defined 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';

Index name cannot be used for the following chauncey:

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

For more information about indexes, see [7].

Reference link:
[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