Mysql Command for querying table indexes show

To view indexes in mysql, we will use the show command. If there are show indexes or show keys, let's take a look at the specific mysql query table index Command show.

MySQL queries table indexes in two command forms:

The Code is as follows:	Copy code
Mysql> SHOW index FROM 'tblname '; Or: Mysql> SHOW keys FROM 'tblname ';

The result obtained by running the preceding command is as follows. The meaning of each column in the result is described below.

The name of the Table.

Non_unique is 0 if the index cannot contain duplicate words. If yes, it is 1.

The name of the Key_name index.

The column serial number in the Seq_in_index index, starting from 1.

Column_name column name.

The way in which the Collation column is stored in the index. In MySQL, there are values 'A' (ascending) or NULL (unclassified ).

The number of unique values in the Cardinality index. You can update analyze table or myisamchk-a by running analyze table. The base number is counted based on the statistical data stored as an integer. Therefore, this value is not required to be accurate even for small tables. The larger the base, the larger the number of machines that MySQL uses the index when performing the union operation.

Sub_part: If a column is partially indexed, it is the number of indexed characters. If the entire column is indexed, the value is NULL.

Packed indicates how the keywords are compressed. If it is not compressed, It is NULL.

Null if the column contains NULL, YES is included. If NO, the column contains NO.

Index_type used index methods (BTREE, FULLTEXT, HASH, RTREE ).

Comment more comments.

Index creation and Deletion

2. Create an index
You can CREATE an INDEX when executing the create table statement, or use the create index or alter table statement to add an INDEX to the TABLE.

1. ALTER TABLE
Alter table is used to create a common index, a UNIQUE index, or a primary key index.

 

The Code is as follows:	Copy code
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)

Table_name is the name of the table to be indexed, and column_list indicates which columns are indexed. When multiple columns are indexed, they are separated by commas. The index name index_name is optional and is time-saving. MySQL assigns a name based on the first index column. In addition, alter table allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.

2. CREATE INDEX
Create index can add normal or UNIQUE indexes to a table.

 

The Code is as follows:	Copy code
Create index index_name ON table_name (column_list) Create unique index index_name ON table_name (column_list)

 

Table_name, index_name, and column_list have the same meaning as the alter table statement, and the index name is not optional. In addition, you cannot use the create index statement to CREATE a primary key index.

3. Index type
When creating an index, you can specify whether the index can contain duplicate values. If not, the index should be created as a primary key or UNIQUE index. For single-column uniqueness indexes, this ensures that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is not repeated.

The primary key index is very similar to the UNIQUE index. In fact, the primary key index is only a UNIQUE index with the name PRIMARY. This indicates that a table can only contain one primary key, because a table cannot have two indexes with the same name.

The following SQL statement adds the PRIMARY KEY index to the students table on the sid.

 

Alter table students add primary key (sid)

4. delete an index
You can use the alter table or drop index statement to delete an INDEX. Similar to the create index statement, drop index can be processed as a statement in alter table. The syntax is as follows.

 

The Code is as follows:	Copy code
Drop index index_name ON talbe_name Alter table table_name drop index index_name Alter table table_name DROP PRIMARY KEY

 

The first two statements are equivalent. The index index_name in table_name is deleted.

The first statement is only used to delete the primary key index. Because a table only has one primary key index, you do not need to specify the index name. If the primary key index is not created, but the table has one or more UNIQUE indexes, MySQL deletes the first UNIQUE index.

If a column is deleted from the table, the index is affected. If you delete a column in an index with multiple columns, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.