MySQL for the basic index add delete check and change operations summary

Create indexes

The syntax for MySQL's index creation is as follows:

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]
The three keywords in brackets represent the type of index created, representing only 1 index, full-text index, and spatial index, respectively. If we do not specify any keywords, the default is a normal index.
index_name
index_name represents the name of the index, which is defined by the user to facilitate administrative operations such as future changes to the index.
index_type
index_type represents the specific implementation of the index. In MySQL, there are two different forms of indexes -- BTREE and HASH. Only BTREE can be used in tables with storage engines MyISAM and InnoDB, whose default value is BTREE; Two types of indexes, HASH and BTREE, can be used in tables with storage engines MEMORY or HEAP, with the default value of HASH.
index_col_name
index_col_name represents the name of the field you want to index, and you can also create a composite index for multiple fields by separating multiple field names with English commas.
In addition, for a field of type CHAR or VARCHAR, we can create an index using only the first part of the field content, by adding a directive of the form (length) after the corresponding field name, indicating that we only need to create an index using the length characters before the field content. Here, we take the username field of User table (type VARCHAR(50)) as an example, using the 6-character prefix of the username field to create the index.

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

Because the first six characters of most fields are usually different, this index is not much slower than an index created using the entire contents of the field. In addition, using part 1 of a field to create an index can greatly reduce the size of the index file, thus saving a lot of disk space and potentially increasing the speed of INSERT operations.

In MySQL, the maximum length of the prefix is 255 bytes. For tables whose storage engine is MyISAM or InnoDB, the prefix is up to 1000 bytes.

It is important to note that in MySQL, for fields of the big data types TEXT and BLOB, the prefix length (length) must be given to create the index successfully.

Note 1: The above syntax for creating indexes also has the following variations:

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

Note 2: 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 for the table is of type MyISAM, InnoDB, or BDB.

Remove the index

The method to delete an index in MySQL is very simple, and the complete syntax is as follows:

-- Deletes the index with the specified name in the specified table 
ALTER TABLE table_name
DROP INDEX index_name;

Here, we write SQL statement to delete the index idx_user_username in the example above. The code details are as follows:

-- Delete name as idx_user_username The index of the 
ALTER TABLE user
DROP INDEX idx_user_username;

Modify the index

There is no direct instruction to modify the index in MySQL. In general, we need to delete the original index and then create an index with the same name according to the need, so as to achieve the operation of modifying the index in a disguised way.

-- First remove 
ALTER TABLE user
DROP INDEX idx_user_username;
-- Create an index with the same name with the modified content 
CREATE INDEX idx_user_username ON user (username(8));

View index

In MySQL, looking at an index in a database table is as simple as using either of the following two commands.

-- If not used before viewing the index user db_name The command specifies the specific database, must be added FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
-- If not used before viewing the index user db_name The command specifies the specific database, must be added db_name. The prefix 
SHOW INDEX FROM [db_name.]table_name