A summary of the basic additions and deletions for indexing in Mysql _mysql

Create an index

MySQL creates the index syntax 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 denote the types of indexes that are created, representing unique indexes, Full-text indexes, and three different index types for spatial indexes. If we do not specify any keywords, the default is the normal index.
Index_name
Index_name represents the name of the index, which is defined by the user, so that administrative actions such as modifications to the index can be made at a later time.
Index_type
Index_type represents the specific implementation of the index, in MySQL, there are two different forms of index--btree index and hash index. You can use only btree in tables where the storage engine is MyISAM and InnoDB, and the default value is Btree, and the hash and memory two types of indexes can be used in tables in the storage engine for heap or btree, with the default value of hash.
Index_col_name
Index_col_name indicates the name of the field where the index needs to be created, and we can also create a composite index for multiple fields, separated by a comma between multiple field names.
In addition, for fields of char or varchar type, we can create an index using only the previous part of the field's content, simply by adding the shape (length) to the corresponding field name to create the index using the length character preceding the field content. Here, for example, we use the username field (type varchar (50)) of the user table to create an index using the 6-character prefix of the username field.

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

Because the first 6 characters of a multiple-digit segment are usually different, this index is not much slower than the index created with the entire contents of the field. In addition, creating an index with a portion of a field can greatly reduce the index file, saving a lot of disk space and potentially increasing the speed of the insert operation.

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

It is important to note that in MySQL, for fields of large data types such as text and blobs, the prefix length (length) must be given to create the index successfully.

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

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

Note 2: In MySQL, you can add an index to a column with a null value or to a column with a data type of text or blob, only if the data table's storage engine is MyISAM, InnoDB, or BDB type.

Delete Index

The method of deleting an index in MySQL is very simple, with the complete syntax as follows:

--Deletes the index of the specified name in the specified table
table_name
DROP index index_name;

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

--Deletes an index
ALTER TABLE user Drop index
idx_user_username named Idx_user_username;

modifying indexes

In MySQL, there is no direct instructions to modify the index, in general, we need to delete the original index, and then create an index of the same name as needed, thus altering the indexing operation in disguise.

--Delete
ALTER TABLE user
drop INDEX idx_user_username first;
--Create INDEX
idx_user_username on user (username (8)) with the same name as the modified content;

View Index

In MySQL, it's easy to see the indexes in a database table, just use any of the following two commands.

--If you do not specify a specific database using commands such as user db_name before viewing the index, you must add the from Db_name to
index from table_name [from db_name]
--If you view the index before , you must add db_name if you do not specify a specific database using commands such as user db_name. Prefix show
INDEX from [Db_name.] table_name