Examples of MySQL SHOW INDEX
Example 1: SHOW INDEX of Table When the Database is in Use
Here we will take an example of an EMPLOYEE table to SHOW INDEX
Syntax:
SHOW INDEX from my_table
WHERE [condition];
Query:
SHOW INDEX from EMPLOYEE;
Output:
Explanation: Here we are not using the WHERE clause as it is not compulsory. There are 15 properties of the table displayed when the query is fired. Let’s understand the output.
- Table: The name of the table is an employee.
- Non_unique: 0 as the table cannot contain duplicates.
- Key_name: PRIMARY as the index is primary.
- Seq_in_index: The column sequence number in index starts with 1.
- Column_name: The column name is EMP_ID.
- Collation: ‘A’ as the column is sorted in ascending order.
- Cardinality: There are 10 unique values in the table.
- Sub_part: NULL as the entire column is indexed.
- Packed: NULL as the key is not packed.
- Null: The column cannot contain NULL values.
- Index_type: The BTREE indexing method was used.
- Comment: There were no comments when the index was created.
- Index_comment: There were no comments when the index was created.
- Visible: YES as the index is visible to the optimizer.
- Expression: The Expression is NULL.
Example 2: SHOW INDEX of the Table Using the Database Name
Here we will take an example of an EMPLOYEE table to SHOW INDEX using database name
Syntax:
SHOW INDEX FROM my_table FROM my_db
WHERE [condition];
SHOW INDEX FROM my_db.my_table
WHERE [condition];
There are 2 ways to SHOW INDEX using the database.
Here we will take an example of database_name = ‘sahil‘ and table name ‘employee‘.
Query:
SHOW INDEX FROM EMPLOYEE FROM sahil;
SHOW INDEX FROM sahil.EMPLOYEE;
Output:
Explanation: Here we are using database ‘sahil‘ and the EMPLOYEE table is stored under this database. Here we are not using the WHERE clause as it is not compulsory. Both queries will give the same output. There are 15 properties of the table displayed when the query is fired. Let’s understand the output.
- Table: The name of the table is an employee.
- Non_unique: 0 as the table cannot contain duplicates.
- Key_name: PRIMARY as the index is primary.
- Seq_in_index: The column sequence number in index starts with 1.
- Column_name: The column name is EMP_ID.
- Collation: ‘A’ as the column is sorted in ascending order.
- Cardinality: There are 10 unique values in the table.
- Sub_part: NULL as the entire column is indexed.
- Packed: NULL as the key is not packed.
- Null: The column cannot contain NULL values.
- Index_type: The BTREE indexing method was used.
- Comment: There were no comments when the index was created.
- Index_comment: There were no comments when the index was created.
- Visible: YES as the index is visible to the optimizer.
- Expression: The Expression is NULL.
MySQL SHOW INDEX
MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995.
MySQL is reputed for its sturdy and quick functioning attributes which involve easy-to-handle features and dependability. MySQL can normally be seen together with dynamic web applications and is generally used to serve languages such as PHP but also other server-side programming languages like Python. In this article, you will discover how the MySQL SHOW INDEX works along with some examples.