Summary: in this tutorial, you will learn how to query index information from a table by using the MySQL
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)0 command.Introduction to MySQL
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)0 commandTo query the index information of a table, you use the
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)0 statement as follows:SHOW INDEXES FROM table_name;
Code language: SQL (Structured Query Language) (sql)To get the index of a table, you specify the table name after the
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)3 keyword. The statement will return the index information associated with the table in the current database.You can specify the database name if you are not connected to any database or you want to get the index information of a table in a different database:
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)The following query is similar to the one above:
SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)Note that
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)4 andSHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)5 are the synonyms of theSHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)6,SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)7 is the synonym of theSHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)3, therefore, you can use these synonyms in theSHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)0 column instead. For example:SHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)Or
SHOW KEYS FROM tablename IN databasename;
Code language: SQL (Structured Query Language) (sql)The
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)0 returns the following information:table
The name of the table
non_unique
1 if the index can contain duplicates, 0 if it cannot.
key_name
The name of the index. The primary key index always has the name of
SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)1.seq_in_index
The column sequence number in the index. The first column sequence number starts from 1.
column_name
The column name
collation
Collation represents how the column is sorted in the index.
SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)2 means ascending,SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)3 means descending, orSHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)4 means not sorted.cardinality
The cardinality returns an estimated number of unique values in the index.
Note that the higher the cardinality, the greater the chance that the query optimizer uses the index for lookups.
sub_part
The index prefix. It is null if the entire column is indexed. Otherwise, it shows the number of indexed characters in case the column is partially indexed.
packed
indicates how the key is packed; NUL if it is not.
null
SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)5 if the column may contain NULL values and blank if it does not.index_type
represents the index method used such as
SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)6,SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)7,SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)8, orSHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)9.comment
The information about the index not described in its own column.
index_comment
shows the comment for the index specified when you create the index with the
SHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)0 attribute.visible
Whether the index is visible or invisible to the query optimizer or not;
SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)5 if it is,SHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)2 if not.expression
If the index uses an expression rather than column or column prefix value, the expression indicates the expression for the key part and also the
SHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)3 column is NULL.Filter index information
To filter index information, you use a
SHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)4 clause as follows:SHOW INDEXES FROM table_name WHERE condition;
Code language: SQL (Structured Query Language) (sql)You can use any information returned by the
SHOW INDEXES FROM table_name IN database_name;
SHOW INDEXES FROM table_name WHERE VISIBLE = 'NO';
Code language: SQL (Structured Query Language) (sql)MySQL
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)0 examplesWe will create a new table named
SHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)7 to demonstrate theSHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)0 command:CREATE TABLE contacts( contact_id INT AUTO_INCREMENT, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(100), phone VARCHAR(20), PRIMARY KEY(contact_id), UNIQUE(email), INDEX phone(phone) INVISIBLE, INDEX name(first_name, last_name) comment 'By first name and/or last name' );
Code language: SQL (Structured Query Language) (sql)The following command returns all index information from the
SHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)7 table:SHOW INDEXES FROM contacts;
Code language: SQL (Structured Query Language) (sql)The output is:
To get the invisible indexes of the
SHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)7 table, you add aSHOW INDEX IN table_name FROM database_name;
Code language: SQL (Structured Query Language) (sql)4 clause as follows:SHOW INDEXES FROM contacts WHERE visible = 'NO';
Code language: SQL (Structured Query Language) (sql)Here is the output:
In this tutorial, you have learned how to use the MySQL
SHOW INDEXES FROM table_name IN database_name;
Code language: SQL (Structured Query Language) (sql)0 statement to get the index information of a table.