Creation and deletion of indexes

Create a normal index

Grammar

CREATE TABLE T1 (

Idint primary KEY,

Name varchar (10),

Sex ENUM (' F ', ' M ', ' UN ')

Index (name)

) Engine=myisam character Set UTF8;

Create an index

Mysql> CREATE TABLE t_1 (id int, Namevarchar (Ten), index (name));

Query OK, 0 rows affected (0.06 sec)

View statements

Mysql> Show CREATE TABLE t_1\g;

1. row***************************

Table:t_1

Create table:create Table ' t_1 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

KEY ' name ' (' name ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Create a unique index

Grammar

CREATE TABLE T1 (

Idint primary KEY,

Name varchar (10),

Sex ENUM (' F ', ' M ', ' UN ')

Uniqueindex id_in (ID)

) Engine=myisam character Set UTF8;

Create an index

Mysql> CREATE TABLE T_2 (

ID int,

Name varchar (10),

Unique index Idinx (ID)

);

Query OK, 0 rows affected (0.24 sec)

View statements

Mysql> Show CREATE TABLE t_2\g;

1. row***************************

Table:t_2

Create table:create Table ' t_2 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

UNIQUE KEY ' Idinx ' (' ID ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Create a single-column index

Grammar

CREATE TABLE T1 (

Idint primary KEY,

Name varchar (10),

Sex ENUM (' F ', ' M ', ' UN ')

Index name_in (name (10))

) Engine=myisam character Set UTF8;

Create an index

Mysql> CREATE TABLE T_3 (

ID int,

Name varchar (10),

-Index Idinx (name (10))

);

Query OK, 0 rows affected (0.06 sec)

View statements

Mysql> Show CREATE TABLE t_3\g;

1. row***************************

Table:t_3

Create table:create Table ' T_3 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

KEY ' Idinx ' (' name ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Create a composite index

Grammar

CREATE TABLE T1 (

Idint NOT NULL,

Name varchar (20),

The age int is not NULL,

Index multi_in (id,name,age (100))

) Engine=myisam character Set UTF8;

Create an index

Mysql> CREATE TABLE T_4 (

ID int,

Name varchar (10),

-Age int,

-Index MUTIIDX (id,name,age)

);

Query OK, 0 rows affected (0.07 sec)

Inserting data

mysql> INSERT INTO T_4 values

(1, ' AAA ', 10),

(2, ' BBB ', 20),

(3, ' CCC ', 30),

(4, ' DDD ', 40),

(5, ' EEE ', 50);

Query OK, 5 rows affected (0.05 sec)

Records:5 duplicates:0 warnings:0

Mysql> select * from T_4;

+------+------+------+

| ID | name | Age |

+------+------+------+

| 1 |   AAA | 10 |

| 2 |   BBB | 20 |

| 3 |   CCC | 30 |

| 4 |   DDD | 40 |

| 5 |   EEE | 50 |

+------+------+------+

5 rows in Set (0.00 sec)

Mysql> explain select Name,age from T_4where id<3\g;

1. row***************************

Id:1

Select_type:simple

Table:t_4

Partitions:null

Type:range

Possible_keys:mutiidx

Key:mutiidx

Key_len:5

Ref:null

Rows:2

filtered:100.00

Extra:using where; Using Index

1 row in Set, 1 Warning (0.00 sec)

ERROR:

No query specified

Mysql> explain select Name,age from T_4where id<3 and age<50\g;

1. row***************************

Id:1

Select_type:simple

Table:t_4

Partitions:null

Type:range

Possible_keys:mutiidx

Key:mutiidx

Key_len:5

Ref:null

Rows:2

filtered:33.33

Extra:using where; Using Index

1 row in Set, 1 Warning (0.00 sec)

ERROR:

No query specified

Mysql> explain select Name,age from T_4where age<50\g;

1. row***************************

Id:1

Select_type:simple

Table:t_4

Partitions:null

Type:index

Possible_keys:null

Key:mutiidx

Key_len:23

Ref:null

Rows:5

filtered:33.33

Extra:using where; Using Index

1 row in Set, 1 Warning (0.00 sec)

ERROR:

No query specified

Note: When you combine an index query, you need to include the keyword at the leftmost column for the index to be used, otherwise you won't use the index.

To create a full-text index

Grammar

CREATE TABLE T1 (

Idint NOT NULL,

Name varchar (20),

The age int is not NULL,

InfoText,

Fulltext Index info_in (info)

) Engine=myisam character Set UTF8;

Create an index

modifying and deleting an index with an ALTER statement

Grammar

Alter table T1 add name_in (name);

Alter table T1 DROP index name_in;

View statements

Mysql> Show CREATE TABLE t_5\g;

1. row***************************

Table:t_5

Create table:create Table ' t_5 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar (DEFAULT NULL)

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Add index

Mysql> ALTER TABLE t_5 add index NAMEIDX (name);

Query OK, 0 rows affected (0.04 sec)

records:0 duplicates:0 warnings:0

View statements

Mysql> Show CREATE TABLE t_5\g;

1. row***************************

Table:t_5

Create table:create Table ' t_5 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

KEY ' Nameidx ' (' name ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Increase the uniqueness Index

Mysql> ALTER TABLE t_5 add unique indexnameIdx1 (name);

Query OK, 0 rows affected (0.05 sec)

records:0 duplicates:0 warnings:0

Mysql> Show CREATE TABLE t_5\g;

1. row***************************

Table:t_5

Create table:create Table ' t_5 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

UNIQUE KEY ' nameIdx1 ' (' name '),

KEY ' Nameidx ' (' name ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Delete Index

mysql> ALTER TABLE t_5 drop INDEXNAMEIDX;

Query OK, 0 rows affected (0.05 sec)

records:0 duplicates:0 warnings:0

View

Mysql> Show CREATE TABLE t_5\g;

1. row***************************

Table:t_5

Create table:create Table ' t_5 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

UNIQUE KEY ' nameIdx1 ' (' name ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

To Modify an index with the CREATE statement

Grammar

Create index name_in on T1 (name);

View

Mysql> Show CREATE TABLE t_5\g;

1. row***************************

Table:t_5

Create table:create Table ' t_5 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

UNIQUE KEY ' nameIdx1 ' (' name ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Add index

Mysql> CREATE index IDIDX on t_5 (ID);

Query OK, 0 rows affected (0.03 sec)

records:0 duplicates:0 warnings:0

View

Mysql> Show CREATE TABLE t_5\g;

1. row***************************

Table:t_5

Create table:create Table ' t_5 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

UNIQUE KEY ' nameIdx1 ' (' name '),

KEY ' Ididx ' (' ID ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Dropping an index with a drop statement

Grammar

Drop index name_in on T1;

View

Mysql> Show CREATE TABLE t_5\g;

1. row***************************

Table:t_5

Create table:create Table ' t_5 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

UNIQUE KEY ' nameIdx1 ' (' name '),

KEY ' Ididx ' (' ID ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Delete

Mysql> DROP index ididx on t_5;

Query OK, 0 rows affected (0.04 sec)

records:0 duplicates:0 warnings:0

View

Mysql> Show CREATE TABLE t_5\g;

1. row***************************

Table:t_5

Create table:create Table ' t_5 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

UNIQUE KEY ' nameIdx1 ' (' name ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

This article from "Confidence snail" blog, declined reprint!

Creation and deletion of indexes