MySQL index creation, view, delete, modify

Create an index

MySQL creates an index with the following syntax:

?

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

The corresponding syntax variable information is as follows:

[unique| Fulltext| SPATIAL]
These three keywords in brackets denote the type of index created, which represents a unique index, a full-text index, and a spatial index of three different index types. 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, to facilitate later management operations such as modifying the index.
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. Only btree is used in tables where the storage engine is MyISAM and InnoDB, and the default value is Btree; hash and btree Two types of indexes can be used in tables where the storage engine is memory or heap, and the default value is hash.
Index_col_name
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 name, indicating that you only need to create the index with the length character in front of the field content. Here we use the User table's username field (type varchar (50)) as an example, using the 6-character prefix of the username field to create an index.

?

1	CREATEINDEX 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.

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

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 successfully create the index.

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

?

12	ALTERTABLE table_nameADD [UNIQUE|FULLTEXT|SPATIAL] INDEXindex_name (index_col_name,...) [USING index_type]

Note 2: In MySQL, you can add an index to a column with null values 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 and its complete syntax is as follows:

?

123	--删除指定表中指定名称的索引ALTERTABLE table_nameDROP INDEXindex_name;

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

?

123	--删除名称为idx_user_username的索引ALTERTABLE userDROP INDEXidx_user_username;

modifying indexes

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

?

12345

--先删除ALTERTABLE userDROP INDEX idx_user_username;--再以修改后的内容创建同名索引CREATE INDEX idx_user_username ON user(username(8));

View Index

In MySQL, it is also very easy to see the indexes in a database table, using just one of the two commands below.

?

1234	--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_nameSHOW INDEXFROM table_name [FROM db_name]--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀SHOW INDEX FROM[db_name.]table_name

MySQL index creation, view, delete, modify