[Source] MySQL View and Create Index commands

Use the ALTER command to add and remove indexes

There are four ways to add an index to a data table:

ALTER TABLE tbl_name ADDPRIMARYKEY (column_list): This statement adds a primary key, which meansThe index value must be unique and cannot be NULL。
ALTER TABLE tbl_name ADDUNIQUEindex_name (column_list): This statement creates an indexmust be unique (in addition to NULL, which can occur multiple times)。
ALTER TABLE tbl_name ADDINDEXindex_name (column_list): AddNormal index, the index value can appear multiple times.
ALTER TABLE tbl_name ADDFULLTEXTindex_name (column_list): This statement specifies that the index is FULLTEXT for Full-text index。


When creating an index, it is recommended to create it directly with the MySQL command line, not with MySQL Workbench to create it.

View the index




· Table

The name of the table.

· Non_unique

If the index cannot include duplicate words, it is 0. If you can, then 1.

· Key_name

The name of the index.

· Seq_in_index

Column sequence number in the index, starting from 1.

· Column_name

Column Name.

· Collation

How are columns stored in the index? In MySQL, there is a value 'A' (ascending) or NULL (no classification).

· Cardinality

An estimate of the number of unique values in the index. It can be updated by running ANALYZE TABLE or myisamchk -a. Cardinals are counted based on statistics that are stored as integers, so the value does not have to be precise even for small tables. The larger the cardinality, the greater the chance that MySQL will use that index when federated is made.

· Sub_part

If the column is only partially indexed, it is the number of characters that are indexed. If the entire column is indexed, it is NULL.

· Packed

Indicates how keywords are compressed. If it is not compressed, it is NULL.

· Null

If the column contains NULL, it contains YES. If not, the column contains NO.

· Index_type

Used indexing methods (BTREE, FULLTEXT, HASH, RTREE).

· Comment