Database Add index __ Database

Source: Internet
Author: User
Tags mysql create
1.MySQL Create an index when creating a data table

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

CREATE Table table name (field name data type [integrity constraint],
                  UNIQUE | Fulltext | SPATIAL] INDEX | KEY
                  [index name] (field name 1 [(length)] [ASC | DESC])
);
UNIQUE: Optional. Represents an index as a uniqueness index. Fulltext, optional. Represents an index as a full-text index. SPATIAL: Optional. Indicates that the index is a spatial index. Index and key: Used to specify that the fields are indexed, one of them can be selected, and the effect is the same. Index name: Optional. Take a new name for the index you created. Field Name 1: Specifies the name of the field that the index corresponds to, which must be a previously defined field. Length: Optional. Refers to the length of the index, which must be a string type before it can be used. ASC: Optional. Represents an ascending arrangement. DESC: Optional. Represents a descending arrangement. MySQL Create a normal index

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

Instance: Create a data table named Index1, and establish a normal index on the ID field in the table.

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

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

View the effect of MySQL creating a normal index in a DOS prompt window. As shown in the following illustration:

As you can see from the illustration above, the run results show that the normal index creation was successful.

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

As you can see from the image above, a normal index named ID has been established on the ID field.

Statement:

      KEY ' id ' (' ID ')

The ID within the parentheses is the name of the field, and the ID outside the left side 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

View the action effect of using the explain statement to see if an index is being used in a DOS prompt window. As shown in the following illustration:

The results shown in the above illustration show that both the Possible_keys and key values are IDs. The description ID index already exists and the index is already used in the query. mysql Create uniqueness index

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

Instance: Create a data table named Index2, set up a unique index on the ID field in the table, and set the order of the ID character Jehi.

1. The SQL code to create a unique index is 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 rope.

See the effect of MySQL creating a unique index in a DOS prompt window. As shown in the following illustration:

As you can see from the above illustration, the results of the run 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 a table in a DOS prompt window. As shown in the following illustration:

As you can see from the illustration above, two unique indexes named IDs and INDEX2_ID are established on the ID field. By doing this, you can increase the query speed of your data.

If you create a index2 table, the ID field does not end uniquely. 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 indexing uses 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 searches.

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

Instance: Create a data table named Index3, and establish a Full-text index named Index3_info on the Info field in the table.

1. The SQL code to create a Full-text index is as follows:

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

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

View the operation effect of MySQL creating a Full-text index in a DOS prompt window. As shown in the following illustration:

As you can see from the illustration above, the execution results of the code show the success of the creation.

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

As you can see from the image above, a Full-text index named Index3_info has been established on the Info field. Attention

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

Full-text indexing is ideal for large datasets and may be less useful for small datasets. MySQL Create Single-column index

A single-column index is an index that is created on a single field in a datasheet. Multiple Single-column indexes can be created in a table. The uniqueness index and the normal index are all single-column indexes.

Instance: Create a data table named INDEX4, and establish a Single-column index named Index4_st on the Subject field in the table.

1. The SQL code to create a Single-column index is as follows:

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

View the effect of MySQL creating a single-column index in a DOS prompt window. As shown in the following illustration:

As you can see from the illustration above, the results of the code execution show the success of the creation.

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

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

Note: The subject field has a length of 30, and the index length of the Index4_st setting is only 10, in order to increase the query speed. For character-type data, you can query for all information without querying for a number of character information in front of it. MySQL creates multiple-column indexes

Creating a multiple-column index creates an index on more than one field in the table.

Instance: Create a data table named INDEX5, and establish a multiple-column index named Index5_ns on the name and sex fields in the table.

1. The SQL code to create a multiple-column index is as follows:

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

View the effect of MySQL creating a multiple-column index in a DOS prompt window. As shown in the following illustration:

As you can see from the illustration above, 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 datasheet. As shown in the following illustration:

As you can see from the illustration above, a multiple-column index named Index5_ns has been established on the name and sex fields.

3. In a multiple-column index, the index is used only when the first field in these fields is used in the query criteria.

First add some data records to the INDEX5 datasheet, and then use the explain statement to see how the index is used. If you are querying by using the Name field only as a query condition. As shown in the following illustration:

As can be seen from the above figure, the values of Possible_keys and key are all Index5_ns. Extra (extra information) shows that indexes are being used. This indicates that the index Index5_ns is already in use when indexing using the Name field.

4. If you are querying using only the sex field as a query condition. As shown in the following illustration:

As you can see from the image above, the values of Possible_keys and keys are null. Extra (extra information) shows that a where condition query is being used without an index. Tips

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

Spatial indexes can be created using the spatial parameter. When creating a spatial index, the table's storage engine must be a MyISAM type. Also, index fields must have non-null constraints.

Instance: Create a data table named index6, and establish 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,
                    GEOMETRY not NULL,
                    SPATIAL INDEX index6_sp
) Engine=myisam;

View the effect of MySQL creating a spatial index in a DOS prompt window. As shown in the following illustration:

As you can see from the illustration above, the results of the code execution show that the space index was created successfully.

2. Use the show CREATE table statement to see the structure of the index6 datasheet. As shown in the following illustration:

As can be seen from the above figure, 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 geometry type. This type is a spatial data type.

Spatial data types include geometry, point, linestring, and polygon types. These spatial data types are rarely used to add index 1 to 2. Add primary key (primary key index)
Mysql>alter TABLE ' table_name ' ADD PRIMARY KEY (' column ')
2. Add unique (unique index)
Mysql>alter TABLE ' table_name ' ADD UNIQUE (
' Column '
)
3. Add index (normal index)
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column ')
4. Add fulltext (full-text indexing)
Mysql>alter TABLE ' table_name ' ADD fulltext (' column ')
5. Adding multiple-column indexes
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column1 ', ' column2 ', ' column3 ')

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.