MySQL index operation command

To create an index:

MySQL creates an index with the following syntax:

CREATE [unique| Fulltext| SPATIAL] INDEX index_name [USING Index_type] on table_name (index_col_name,...)

The corresponding syntax variable information is as follows:

[unique| Fulltext| Spatial]: Three keywords in brackets represent the type of index created , they represent unique indexes, full-text indexes, spatial indexes , and three different index types. If we do not specify any keywords, the default is the normal index.

Index_name: Represents the name of the index, which is defined by the user, so that the index can be modified for later management operations.

Index_type: Indicates How the index is implemented , in MySQL, there are two different forms of indexing----btree index and hash index. only btree is used in tables where the storage engine is MyISAM and InnoDB, and the default value is Btree, and hash and btree two types of indexes can be used in tables where the storage engine is memory or heap. Its default value is hash.

Index_col_name: Represents the name of the field that needs to be indexed, and we can also create a composite index for multiple fields, just by separating the names of multiple fields with commas.

In addition, for fields of char or varchar type, we can also create an index using only the part of the field that precedes it, simply by adding a shape (length) to the corresponding field, indicating that the index needs to be created using the length characters in front of the field content.

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

Because the first 6 characters of a multi-digit field are usually different, this index is not much slower than the index created by using the entire contents of the field. In addition, creating an index using part of a field can make the index file much smaller, saving a lot of disk space and potentially increasing the speed of the insert operation.

It is important to note that: in MySQL, for fields that have a large data type of text and blog, you must give the prefix length (length) to successfully create the index.

The syntax for creating an index above also has the following variants:

ALTER TABLE table_name ADD [unique| Fulltext| SPATIAL] INDEX index_name (index_col_name,...) [USING Index_type]

In MySQL, you can add an index to a column with null values or a column with a data type of text or BLOB only if the table's storage engine is MYISAM,INNODB and BDB types.

To delete an index:

The method of deleting an index in MySQL is very simple and its complete syntax is as follows:

ALTER TABLE table_name DROP INDEX index_name;

To modify an index:

In MySQL, there is no direct instruction to modify the index, in general, we first delete the original index, and then create an index with the same name as necessary, in a disguised way to implement the operation of modifying the index.

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 very easy to see the index pages in a database table with just one of the following two commands:

SHOW INDEX from table_name [from db_name];

SHOW INDEX from [Db_name.] table_name;

MySQL index operation command