MySQL Index Operation Command Detailed

Create an index:

The syntax for MySql to create an index is as follows:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name,...)

The corresponding grammar variable information is as follows:
[UNIQUE | FULLTEXT | SPATIAL]: Three keywords in brackets indicate the type of index created. They represent three different types of index, namely, 1-only index, full-text index, and spatial index.If we do not specify any keywords, the default is a normal index.

index_name: The name of the index, which is defined by the user, so that the index can be modified and managed later.

index_type: Represents how an index is implemented. In MySql, there are two different forms of index - the BTREE index and the HASH index.Only BTREE can be used in tables where the storage engine is MYISAM and INNODB, and its default value is BTREE;Two types of indexes, HASH and BTREE, can be used in tables where the storage engine is MEMORY or HEAP, with the default value of HASH.

index_col_name: Indicates the name of the field that needs to be indexed. We can also create a composite index for multiple fields by separating them with English commas.

In addition, for fields of type char or varchar, we can use only the first part of the field content to create the index, just append a directive like (length) after the corresponding field to indicate that only length characters in front of the field content are needed to create the index.

CREATE INDEX idx_user_username ON user (username(6));

Since the first six characters of most fields are usually different, this index will not be much slower than the one created using the entire contents of the field.In addition, creating an index using a portion of the field can reduce the size of the index file, save a lot of disk space, and possibly increase the speed of INSERT operations.

It is important to note that in MySql, for fields with large data types such as TEXT and BLOG, the prefix length (length) must be given to successfully create the index.

The above indexing syntax also has the following variations:

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]

In MySql, you can only add an index to a column with an NULL value or a column with a data type of TEXT or BLOB if the storage engine of the table is MYISAM, INNODB, and BDB.

Delete index:
The method to delete an index in MySql is very simple, and its full syntax is as follows:

ALTER TABLE table_name DROP INDEX index_name;

Modify Index:

There is no direct instruction to modify the index in MySql. Generally, we delete the original index and create an index with the same name as needed to modify the index in disguised form.

// Delete index first 
ALTER TABLE table_name DROP INDEX index_name;
// Create an index with the same name 
CREATE INDEX index_name ON table_name (index_col_name,...);

View index:

In MySql, it is easy to view index pages in a database table by using only one of the following two commands:

SHOW INDEX FROM table_name [FROM db_name];

SHOW INDEX FROM [db_name.]table_name;