mysql
  1. mysql-show-indexes
PreviousNext

Show Indexes - (MySQL Indexes)

In MySQL, indexes are used to improve the performance of queries on large tables. In this tutorial, we'll discuss how to show indexes in MySQL using the "SHOW INDEXES" command.

Syntax

The syntax for using the "SHOW INDEXES" command in MySQL is as follows:

SHOW INDEXES FROM table_name [WHERE expr];

Here, "table_name" is the name of the table you want to show the indexes for, and "expr" is an optional expression that limits the output to specific indexes.

Example

Let's say we have a table called "users" that has three columns: "id", "name" and "email". We've added two indexes to the table, one on the "id" column and one on the "email" column. Here's how we can show the indexes:

SHOW INDEXES FROM users;

This will output the following:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0          | PRIMARY  | 1            | id          | A         | 3           | <null>   | <null> |      | BTREE      |         |               |
| users | 1          | email    | 1            | email       | A         | 3           | <null>   | <null> | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Output

The "SHOW INDEXES" command will output information about all the indexes in the specified table. The output will include the following columns:

  • Table: The name of the table.
  • Non_unique: Indicates whether the index allows for duplicate values.
  • Key_name: The name of the index.
  • Seq_in_index: The column's position within the index.
  • Column_name: The name of the column.
  • Collation: The collation order for the column.
  • Cardinality: An estimate of the number of unique values in the column.
  • Sub_part: For indexed columns with prefixes, this is the number of characters in the prefix.
  • Packed: Indicates whether the bytes for the column are packed.
  • Null: Indicates whether the index allows NULL values.
  • Index_type: The index method used (e.g. BTREE).
  • Comment: Any additional information about the index.
  • Index_comment: Any additional comments about the index.

Explanation

In the example above, we used the "SHOW INDEXES" command to show the indexes in the "users" table. The output showed information about the two indexes we created: one on the "id" column (which is the primary key) and one on the "email" column.

Use

The "SHOW INDEXES" command is useful for seeing which columns have been indexed in a table, and how those indexes are configured. It can be used to optimize the performance of queries on large tables.

Important Points

  • The "SHOW INDEXES" command can be shortened to "SHOW INDEX" or "SHOW KEYS".
  • The output of "SHOW INDEXES" can be filtered using the WHERE clause.
  • To create an index on a column, use the "CREATE INDEX" command.
  • You can remove an index from a table using the "DROP INDEX" command.

Summary

In this tutorial, we discussed how to show indexes in MySQL using the "SHOW INDEXES" command. We covered the syntax, example, output, explanation, use, and important points of this command. By using the "SHOW INDEXES" command, you can better understand the indexes in your tables and optimize your queries for better performance.

Drop IndexUnique Index
Published on: