MySQL index Operation Command Summary

Syntax for creating an index: CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEXindex_name [USINGindex_type] ONtbl_name (index_col_name ,...) index_col_name: col_name [(length)] [ASC | DESC] for CHAR and VARCHAR columns, you can create an index using only one part of the column. When creating an index

Syntax for creating an INDEX: CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name ,...) index_col_name: col_name [(length)] [ASC | DESC] for CHAR and VARCHAR columns, you can create an index using only one part of the column. When creating an index

Create an index

The syntax for creating an index is:

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

Index_col_name:
Col_name [(length)] [ASC | DESC]

For CHAR and VARCHAR columns, you can create an index using only one part of the column. When creating an index, use the col_name (length) syntax to compile the index for the prefix. The prefix contains the first length characters of each column value. BLOB and TEXT columns can also be indexed, but the prefix length must be given.

The statements shown here are used to create an index. The index uses the first 10 characters of the column name.

Create index part_of_name ON customer (name (10 ));

Because the first 10 characters of most names are usually different, this index is not much slower than the index created using the column's full name. In addition, using a part of the column to create an index can greatly reduce the index file, which saves a lot of disk space and may increase the INSERT operation speed.

The prefix cannot exceed 255 bytes. For MyISAM and InnoDB tables, the prefix is up to 1000 bytes. Note that the prefix length is measured in bytes, while the prefix length in the create index statement indicates the number of characters. This must be taken into consideration when specifying the prefix length of a column using the multi-byte character set.

In MySQL:

· You can add an index to a column with a NULL value only when you are using the MyISAM, InnoDB, or BDB table type.

· You can add an index to the BLOB or TEXT column only when you are using the MyISAM, BDB, or InnoDB table type.

An index_col_name protocol can end with ASC or DESC. These keywords can be expanded in the future to specify the storage of incremental or descending index values. Currently, these keywords are analyzed but ignored. index values are stored in ascending order.

Some storage engines allow you to specify the index type when creating an index. Index_type specifies that the syntax of the statement is USING type_name. The type_name values supported by different storage engines are shown in the following table. If the column has multiple index types, if index_type is not specified, the first type is the default value.

Storage Engine	Allowed index types
MyISAM	BTREE
InnoDB	BTREE
MEMORY/HEAP	HASH, BTREE

Example:

Create table lookup (id INT) ENGINE = MEMORY;
Create index id_index using btree on lookup (id );

TYPE type_name can be used as a synonym for USING type_name to specify the index TYPE. However, USING is the preferred format. In the index specification syntax, the index name located before the index TYPE cannot use TYPE. This is because, unlike USING, TYPE is not a reserved word and is considered an index name.

If the specified index type is invalid in the specified storage engine, but other index types are suitable for the engine and do not affect the query function, the engine should use this type.

FULLTEXT indexes can only be indexed for CHAR, VARCHAR, and TEXT columns, and can only be compiled in MyISAM tables.

SPATIAL indexes can only be used to index SPATIAL columns and can only be used in MyISAM tables.

Use alter to create an index

Alter table table_name add index index_name (column_list );
Alter table table_name add unique (column_list );
Alter table table_name add primary key (column_list );

Query Indexes

Show index from table_name;

Delete Index

Drop index index_name on table_name;
Alter table table_name drop index index_name;
Alter table table_name drop primary key;

In the preceding two statements, the index index_name in table_name is deleted. In the last statement, the primary key index is used only to delete the primary key index. Because a table may only have one primary key index, you do not need to specify the index name.