How to Show Indexes in MySQL Tables & Database

Managing data from small or large databases is a daily task of database administrators and developers. Frequently, they need to search for specific data processing thousands of rows. This can be complicated with large databases and may lead to potential performance issues.

For example, you need to retrieve a list of products with total sales equal to or less than the specified value - 20000. You execute a SELECT * FROM query with the WHERE clause, and MySQL scans each row one by one to check if it satisfies the specified condition. In the output, the matching rows are added to the result to be returned. If MySQL cannot find the required values, it scans each row until the end of the table. What if the data is fetched from a large table? This could waste a significant amount of your time and resources and lead to poor performance. So, indexes may help resolve performance issues with large tables.

An index in MySQL is a data structure that may speed up data retrieval on a table. It arranges values in such a way, so MySQL can quickly find specific rows based on indexed columns without having to scan the full table.

In the article, we'll explore the basics of indexes and their different types available in MySQL. We'll also examine methods for showing indexes and querying specific index information.

Learn MySQL index types

As discussed, a MySQL index is a type of database structure aimed at accelerating a particular database-related operation. MySQL indexes help create, remove, search, and fetch a list of records by scanning the values matching the search criteria in the table data.

It is possible to create an index using the following statements:

CREATE INDEX:

CREATE INDEX index_name ON table_name (indexcolumn1, indexcolumn2, …);

CREATE TABLE:

CREATE TABLE table_name (column1 CHAR (30) NOT NULL, INDEX (column1));

ALTER TABLE:

ALTER TABLE table_name ADD INDEX (column1, column2);

MySQL supports the following types of indexes:

Primary Key index is automatically created by MySQL when you define a primary key.
Unique index ensures that each value in the indexed columns is unique. Unlike the primary key index, it allows NULL values.
Full-text index is used for full-text searches in text columns.
Composite index consists of multiple columns and allows for efficient querying based on combinations of these columns.
Spatial index is used to index columns with spatial data types, such as geometry or geography.
Invisible index is not treated by the query optimizer but remains updated when running INSERT/UPDATE/DELETE statements.
Descending index allows sorting data in descending order.

Show indexes in a MySQL table

To show indexes in a MySQL database, use the SHOW INDEX statement. It returns information about the indexes associated with the table specified in the FROM clause. The syntax of the statement is as follows:

SHOW INDEXES FROM table_name;

To get index information from a different database, add the IN clause to the statement:

SHOW INDEXES FROM table_name 
IN database_name;

To illustrate, let us view all the indexes for the actor table. The query returns the following index details:

Table: Name of the specified table.
Non-unique: Indicates whether the index can have duplicates (1) or not (0).
Key_name: Name of the index, where the primary key index is always called PRIMARY.
Seq_in_index: Sequence number of the column in the index, starting from 1.
Column_name: Name of the column.
Collation: Sorting order of the column in the index (A for ascending, B for descending, or NULL for not sorted).
Cardinality: Estimated number of unique values in the index.
Sub_part: Index prefix, showing NULL for full column indexing or the number of indexed characters for partial indexing.
Packed: Indicates how the key is packed.
Null: Indicates whether the column contains NULL values (YES) or not (blank).
Index_type: Defines the method used for the index: BTREE, HASH, RTREE, or FULLTEXT.
Comment: Additional information about the index.
Index_comment: Displays any specified comments when creating the index using the COMMENT attribute.
Visible: Shows whether the index is visible (YES) or invisible to the query optimizer (NO).
Expression: Represents the key part if the index uses an expression instead of a column or column prefix value; the column_name column is NULL in such cases.

As an alternative to the SHOW INDEX statement, you can use dbForge Studio for MySQL, a powerful IDE designed for MySQL database development, management, and administration. It is a full-featured MySQL GUI solution for query building, schema and data comparison, SQL script editing, database backup and restore, performance monitoring, user management, and version control integration. Being ideal for developers and administrators, it facilitates tasks and enhances productivity across different aspects of MySQL database management.

To get started, open the Studio to view the indexes for the specific MySQL table. On the ribbon, select View > Object Viewer to open the Object Viewer document. The list of databases available on the connected server opens. Double-click the required database to access the objects within the database. The objects grouped by category, such as Tables, Views, Procedures, Functions, Triggers, and Events, appear on the page. To proceed, double-click the Tables group and the table name for which you want to see the indexes. On the next page, you'll see the table components, including Columns, Constraints, Indexes, and Triggers - double-click Indexes to view the index information.

Show indexes using dbForge Studio for MySQL

List all indexes in MySQL by code

Now, let us check all indexes that exist in a specific MySQL database querying the following SELECT statement:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_SCHEMA = 'your_database';

In the query, replace your_database with the MySQL database name for which you want to view the indexes.

List all indexes for the specified MySQL database using a SQL code

The query returns a list of indexes across all tables in the specified database. The output displays a grid in which each row contains the table name and the corresponding index name.

Moreover, you can get specific information about indexes of all tables. To do this, modify the SELECT query by selecting additional columns from the INFORMATION_SCHEMA.STATISTICS table:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX,
    CARDINALITY,
    INDEX_TYPE
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_SCHEMA = 'your_database';

The output displays the following index information: the table where the index exists, the index name, the column it is based on, and uniqueness, which indicates whether the index allows duplicate values (0 for unique indexes, 1 for non-unique indexes). In addition, it shows the sequence number of the column within the index, the number of unique values in the indexed column, and the type of index.

Show detailed information for all indexes in a MySQL database

If you need to display indexed columns from all tables in a specific database, use the following script:

SELECT
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLUMNS
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_SCHEMA = 'your_database'
GROUP BY
    TABLE_NAME,
    INDEX_NAME;

In the example, enter the required database instead of your_database and execute the query:

Display indexes from all tables in a specific database

Find all indexes in MySQL using a GUI tool

With dbForge Studio for MySQL, you can easily find indexes for a specific table without using any code. Navigate to View and select Database Explorer on the ribbon to display the database objects. In Database Explorer, expand the database node of the required database to display a list of database objects such as tables, views, procedures, functions, triggers, and events. Then, expand the Tables node and the table whose indexes you want to view. After that, expand the Indexes node and see the index. Double-clicking the index will open the Table Editor > Indexes tab. At the bottom of the editor, you can view the text body of the index.

Alternatively, you can find all indexes using the Find object feature. To do this, in Database Explorer, right-click the required connection and select Find Object. In the Search document that opens, do the following:

In the Search box, type index.
From the Database dropdown list, select the database for which you want to search indexes. Note that you can choose multiple databases.
In the Search box, click Start Search to find indexes.

The grid appears, where you can see the tables with indexes, the database associated with each table, the object type, and the part where the index has been found. The Search document also displays the script of the object and highlights the index.

Sort index results in MySQL

Now, let us demonstrate how to sort the results returned by the SELECT statement. In MySQL, you can achieve this by using the ORDER BY clause in your query. For example, add the DESC keyword after the column name in the SELECT query to sort the results in descending order. Then, replace your_database with the name of the required MySQL database.

As you can see, the query sorts the results by TABLE_NAME and then by INDEX_NAME in ascending order.

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_SCHEMA,
    INDEX_NAME,
    COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME DESC, INDEX_NAME DESC;

In dbForge Studio for MySQL, you can easily change the order of index results using the sort glyph in the results grid. Alternatively, right-click a column header and select Sort Ascending or Sort Descending.

Filter index results in MySQL

In this part, we'll explore how to filter index results using text-based filtering techniques and methods based on specific values.

To filter results based on text, use the WHERE clause in the query. For example, run the following query to show the indexes with the text pattern you want to filter by in the sakila database.

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'sakila'
  AND TABLE_NAME LIKE '%actor%';

The query filters rows based on the condition and returns all indexes from the table that includes the actor text in the name.

Filter index results based on specific text pattern

MySQL also supports regular expressions for advanced text-based filtering. The following query will return the rows filtered according to the specified condition where the TABLE_NAME column matches the regular expression pattern specified by 'actor'.

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'sakila'
  AND TABLE_NAME REGEXP 'actor';

As you can see, this query returns information about database objects (such as tables, indexes, etc.) stored in the sakila database schema where the table names match the specified regular expression pattern - actor.

Also, you can set a filtering condition to match the exact value using the = operator in the WHERE clause.

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'sakila'
    AND INDEX_NAME = 'idx_actor_last_name';

To make it easy, use the Studio to quickly and easily filter data based on the specified condition from the results grid. To filter indexes by name, click the filter icon on the column header and select the specific index from the dropdown list or Custom to set a user-defined condition in the Custom AutoFilter dialog. Then, select the operator, specify the value, and click OK to save the changes.

Specify the condition using dbForge Studio for MySQL

The output will display the result matching the specified condition. If you want to edit the condition, click Edit Filter at the bottom of the grid. To remove the condition, clear the checkbox next to it.

Conclusion

In the article, we have reviewed the basics and types of indexes and their usage in queries to improve database performance. We have also covered how to get detailed information about indexes using the SHOW INDEX statement, which can help fine-tune performance and ensure efficient data retrieval. We have also explored different ways to filter index results and find specific indexes using the SELECT queries and dbForge Studio for MySQL, a universal IDE for database development and management. In addition to creating queries in a SQL editor, you can evaluate other advanced features and tools, such as a visual Query Builder for MySQL to design tables, JOINs, and set conditions in an easy-to-use interface, or Master-Detail Browser to build, view, and filter data in the Design view.