MySQL creates an index when creating a data table

Reprint: http://www.baike369.com/content/?id=5478 MySQL creates an index when creating a data table

When creating a table in MySQL, you can create an index directly. The basic syntax format is as follows:

[ integrity constraints ],                  [UNIQUE | Fulltext | SPATIAL]indexKEY                  [ index name ][ASC | DESC]));

• UNIQUE: Optional. Represents the index as a uniqueness index.
• Fulltext, optional. Represents the index as a full-text index.
• SPATIAL: Optional. Represents the index as a spatial index.
• Index and key: Used to specify that the field is indexed, both of which can be selected, and the function is the same.
• Index name: Optional. Take a new name for the created index.
• Field Name 1: Specifies the name of the field that corresponds to the index, which must be the field defined previously.
• Length: Optional. Refers to the length of the index, which must be a string type before it can be used.
• ASC: Optional. Represents ascending order.
• DESC: Optional. Represents a descending arrangement.

MySQL Create normal index

When creating a normal index, you do not need to add any unique, fulltext, or spatial parameters.

Example: Create a data table named Index1 to establish a normal index on the ID field in the table.

1. Create the SQL code for the normal index as follows:

CREATE TABLE index1 (id INT,                    name VARCHAR (),                    sex BOOLEAN,                    INDEX(id) );

In the DOS prompt window, see how MySQL creates a normal index. As shown in the following:

As you can see, the result of the run shows that the normal index was created successfully.

2. Use the Show CREATE table statement to view the structure of the table. As shown in the following:

As you can see, an ordinary index named ID has been established on the ID field.

Statement:

      KEY ' id ' (' ID ')

The ID inside the parentheses is the field name, and the ID outside the left of the parentheses is the index name.

3. Use the explain statement to see if the index is being used. The SQL code is as follows:

    EXPLAIN SELECT * from index1 where id=1 \g

In the DOS prompt window, view the effect of using the explain statement to see if the index is being used. As shown in the following:

The results in the Possible_keys show that both the value of both the and key are IDs. The description ID index already exists and the index is already used in the query.

MySQL Create a Uniqueness index

If you use the unique parameter to constrain, you can create a unique index.

Example: Create a data table named Index2, establish a uniqueness index on the ID field in the table, and set the ID Word ascending order.

1. Create a unique index of SQL code as follows:

CREATE TABLE index2 (id INT UNIQUE,                    name VARCHAR)                    UNIQUE INDEX index2_id(id ASC) ;

INDEX2_ID is a new name caused by the uniqueness of the cable.

In the DOS prompt window, see how MySQL creates a unique index. As shown in the following:

As you can see, the running results show that the creation was successful.

2. Use the show CREATE table statement to view the structure of the table. The SQL code is as follows:

SHOW CREATE TABLE index2 \g

View the effect of using the show CREATE table statement to view the structure of the table in the DOS prompt window. As shown in the following:

As you can see, two unique indexes named ID and INDEX2_ID are established on the ID field. In doing so, you can increase the query speed of your data.

If you create a index2 table, the ID field does not end with uniqueness. As shown below:

CREATE TABLE index2 (id INT,                    name VARCHAR)                    UNIQUE INDEX index2_id(id ASC) ;

You can also successfully create a unique index named INDEX2_ID on the ID field. However, this may not achieve the purpose of increasing the query speed.

MySQL Create full-text index

Full-text indexes use the fulltext parameter and can only be created on fields of char, varchar, or text type.

Full-text indexing can be used for full-text search.

Both the MyISAM storage engine and the InnoDB storage engine now support full-text indexing.

Example: Create a data table named Index3 that establishes a full-text index named Index3_info on the Info field in the table.

1. Create the SQL code for the full-text index as follows:

CREATE TABLE index3 (id INT,                    info VARCHAR (),                    FULLTEXT INDEX index3_info(info) ) Engine=myisam;

If you set Engine=innodb, you can create a full-text index on the InnoDB storage engine.

In the DOS prompt window, see how MySQL creates full-text indexes. As shown in the following:

As you can see, the execution results of the code show that the creation was successful.

2. Use the show CREATE table statement to view the structure of the INDEX3 data table. As shown in the following:

As you can see, a full-text index named Index3_info has been established on the Info field.

Attention

I'm using MySQL version 5.6.19, and I can already create a full-text index in the InnoDB storage engine.

Full-text indexing is well suited for large datasets, and may be less useful for small datasets.

MySQL creates a single-column index

A single-column index is an index that is created on a single field in a data table. You can create multiple single-column indexes in a table. Both the uniqueness index and the normal index are single-column indexes.

Example: Create a data table named INDEX4 that establishes a single-column index named Index4_st on the Subject field in the table.

1. Create the SQL code for the single-column index as follows:

CREATE TABLE index4 (id INT,                    subject VARCHAR (),                    INDEX index4_st(subject(10)) );

In the DOS prompt window, see how MySQL Creates a single-column index. As shown in the following:

As you can see, the results of the code execution show that the creation was successful.

2. Use the show CREATE table statement to view the structure of the INDEX4 data table. As shown in the following:

As you can see, a single-column index named Index4_st has been established on the subject field.

Note: The subject field length is 30, and the Index4_st setting has an index length of only 10, which is done to improve query speed. For character-type data, you can query only the character information in front of it without querying all the information.

MySQL create multi-column index

Creating a multicolumn index creates an index on more than one field in the table.

Example: Create a data table named INDEX5 that establishes a multicolumn index named Index5_ns on the name and sex fields in the table.

1. Create the SQL code for the Multi-column index as follows:

CREATE TABLE index5 (id INT,                    name VARCHAR (),                    sex CHAR (4),                    INDEX index5_ns(name,sex) );

In the DOS prompt window, see how MySQL creates multi-column indexes. As shown in the following:

As you can see, the execution results of the code show that the Index5_ns index was created successfully.

2. Use the show CREATE table statement to view the structure of the INDEX5 data table. As shown in the following:

As you can see, a multicolumn index named Index5_ns has been established on the name and sex fields.

3. In a multicolumn index, the index is used only if the first field in these fields is used in the query criteria.

Add some data records to the INDEX5 data table first, and then use the Explain statement to view the usage of the index. If you are querying using the Name field only as a query condition. As shown in the following:

As you can see, the values of Possible_keys and key are Index5_ns. Extra (additional information) shows that the index is being used. This indicates that index Index5_ns is already in use when indexed using the Name field.

4. If you are querying only using the Sex field as the query criteria. As shown in the following:

As you can see, the values for both Possible_keys and key are null. Extra (additional information) shows that the Where condition query is being used instead of using an index.

Tips

When using multi-column indexes, it is important to note that indexes are only triggered when the first field in the index is used. If the first field in the index is not used, the multi-column index will not work. Therefore, you can consider optimizing multi-column indexes when optimizing query speed.

MySQL Create spatial index

Spatial indexes can be created using the spatial parameter. When you create a spatial index, the storage engine for the table must be of type MyISAM. Also, the index field must have a non-null constraint.

Example: Create a data table named index6 that establishes a spatial index named INDEX6_SP on the Space field in the table.

1. The SQL code to create the spatial index is as follows:

CREATE TABLE index6 (id INT,                    space GEOMETRY not NULL,                    SPATIAL INDEX index6_sp(space) ) Engine=myisam;

In the DOS prompt window, see how MySQL creates spatial indexes. As shown in the following:

As you can see, the result of the code execution shows that the spatial index was created successfully.

2. Use the show CREATE table statement to see the structure of the INDEX6 data table. As shown in the following:

As you can see, a spatial index named INDEX6_SP has been established on the space field.

Note that the space field is non-empty and the data type is the geometry type. This type is a spatial data type.

Spatial data types include geometry, point, linestring, and polygon types. These spatial data types are seldom used in the usual

MySQL creates an index when creating a data table