To create an index with an ALTER TABLE statement

7.2.3 to create an index with an alter TABLE statement

on tables that already exist, you can create indexes directly on one or several of the fields on the table by using the ALTER TABLE statement. The basic form is as follows:

ALTER table name ADD [UNIQUE | Fulltext | SPATIAL] INDEX

Index name (property name [(length)] [ASC | DESC]);

The parameters are the same as the parameters in the above two ways.

1 . Create a normal index

Under example 7-13, an index named Index13_name is established on the Name field in the Example0 table. The SQL code is as follows:

ALTER TABLE example0 ADD INDEX index13_name (name (20));

Before creating an index using the ALTER TABLE statement, execute the Show CREATE TABLE statement to see the structure of the EXAMPLE0 table. The SHOW CREATE Table statement performs the following results:

MySQL> SHOW CREATE TABLE example0 \g

1. Row **************************

Table:example0

Create table:create Table ' Example0 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar (DEFAULT NULL),

' Sex ' tinyint (1) DEFAULT NULL,

KEY ' index7_id ' (' ID ')

) ENGINE=InnoDB DEFAULT CHARSET=UTF8

1 row in Set (0.00 sec)

The results show that there are only index7_id indexes on the EXAMPLE0 table. The following ALTER TABLE statement is executed to create the Index13_name index. The ALTER Table statement performs the following results:

MySQL> ALTER TABLE example0 ADD INDEX index13_name (name (20));

Query OK, 0 rows affected (0.01 sec)

records:0 duplicates:0 warnings:0

The running results show that the creation was successful, using the show CreateTable statement to view the structure of the EXAMPLE0 table. Shown below:

MySQL> SHOW CREATE TABLE example0 \g

1. Row ***************************

Table:example0

Create table:create Table ' Example0 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar (DEFAULT NULL),

' Sex ' tinyint (1) DEFAULT NULL,

KEY ' index7_id ' (' id '),

KEY ' Index13_name ' (' name ')

) ENGINE=InnoDB DEFAULT CHARSET=UTF8

1 row in Set (0.00 sec)

results can be seen, name field has been created with a name of Index13_name the index.

2 . Create a Uniqueness Index

The example 7-14 " below the index14 in the table course_id field, create a name named index14_id Uniqueness Index. the SQL code is as follows:

ALTER TABLE index14 ADD UNIQUE INDEX index14_id (course_id);

where index14_id are indexed nouns; unique is used to set the index to a unique index; the course_id field in table index14 can have a uniqueness constraint, or it can have no uniqueness constraints.

3 . To create a full-text index

Under example 7-15, a full-text index named Index15_info is established on the Info field in the index15 table. The SQL code is as follows:

ALTER TABLE index15 ADD fulltext INDEX index15_info (info);

where Fulltext is used to set the index to full-text indexing, the storage engine for table index15 must be of type MyISAM, and the info field must be of type char, varchar, and text.

4 . Create a single-column index

Under example 7-16, a single-column index named INDEX16_ADDR is established on the Address field in the Index16 table. The Address field has a data type of varchar (20) and the data type of the index is char (4). The SQL code is as follows:

ALTER TABLE index16 ADD INDEX index16_addr (Address (4));

This allows you to query only the first 4 characters of the Address field, without requiring all queries.

5 . Creating Multi-column Indexes

Under example 7-17, a multicolumn index named Index17_na is established on the name and address fields in the Index17 table. The SQL code is as follows:

ALTER TABLE index17 ADD INDEX Index17_na (name, address);

after the index has been created, the query criteria must have name field to use the index.

6 . Create a spatial index

The example 7-18 " below the index18 in the table Line field, create a name named Index18_line the Multi-column index. the SQL code is as follows:

ALTER TABLE index18 ADD SPATIAL INDEX index18_line (line);

where spatial is used to set the index as a spatial index; the storage engine for table index18 must be a MyISAM type; the line field must be non-empty and must be a spatial data type.

To create an index with an ALTER TABLE statement