Understanding and Inspecting Indexes in MySQL

An index helps MySQL find the data it needs without scanning an entire table. For a handful of rows, this might not seem important. But in a table with hundreds of thousands or millions of rows, the difference between having a lookup structure and lacking one can be dramatic.

Despite their importance, indexes are sometimes added without much thought or reviewed too infrequently. As data and queries change, a search key that once helped may now be hurting performance. That’s why inspecting and understanding existing indexes is not just a task for optimization but an ongoing part of maintaining a healthy database.

Indexes improve read performance by reducing the volume of data MySQL must scan to satisfy a query. Instead of evaluating every row sequentially, the database can traverse a search structure, usually a balanced tree like BTREE, to locate matching records far more efficiently. For well-designed queries, this can reduce response times from seconds to milliseconds.

However, improved read performance comes with trade-offs. Any time a row is inserted, updated, or deleted, MySQL must modify the related lookup designs to reflect the changes. As the number and complexity of indexes increases, so does the overhead for write operations. While InnoDB’s background processes and fine-grained locking help mitigate this, write latency can grow, especially in workloads with frequent updates or high concurrency.

Storage consumption is another consideration. Indexes consume disk space proportional to the size and type of columns they reference. Multi-column and prefix keys can grow rapidly, and in some scenarios, the combined size of a table’s indexes may equal or exceed the size of the data itself. This can impact not only storage costs but also backup, restore, and replication performance.
Balancing these factors requires a deep understanding of how the application queries the data, how often the data changes, and what trade-offs are acceptable between read and write performance. Indexing strategies should reflect actual workload patterns and maintenance requirements, not just general best practices or assumptions.

If you're looking for guidance on creating indexes or using EXPLAIN to analyze query plans, we cover those topics in separate articles.

While developers can adopt many indexing strategies, MySQL fundamentally supports a few core types:

• Primary Key: Uniquely identifies each row. Every InnoDB table is required to have one, either explicitly or automatically created.

• Unique: Enforces uniqueness constraints, often applied to columns like email addresses, user IDs, or other natural keys.

• Full-Text: Optimized for efficient text search, supporting natural language queries across large string fields. (Covered in more depth in our Full-text indexing guide.)

• Spatial: Supports indexing of spatial (geometric) data types.

• Composite: Indexes multiple columns together, improving performance for queries that filter, sort, or join using more than one field. Column order is critical for effectiveness.

• Prefix: Indexes only a portion of a column’s values, commonly used with long strings such as URLs or file paths to reduce index size and overhead.

Most MySQL search paths rely on BTREE structures, offering balanced, ordered storage suitable for a wide variety of query patterns. HASH is also available, but is typically confined to MEMORY tables or specific storage engines and has limitations regarding range queries and sorting.

A common misconception is that MySQL can merge multiple indexes to speed up a single query. In reality, MySQL typically selects one search key per table per query. While the optimizer can occasionally merge multiple lookup paths for certain OR conditions or UNION queries, this behavior is unpredictable. Careful alignment with the structure and filtering logic of the queries is essential.

To inspect the indexes associated with a table, the most commonly used commands are:

AND

This retrieves a result set describing all keys currently defined on the table, including primary keys, unique constraints, user-created secondary indexes, and any lookup structures automatically created by the storage engine. MySQL will display both explicitly named indexes and those assigned default names by the system.

If the table exists outside the default schema, or if you want to be explicit, you can specify the database:

The command queries MySQL’s internal metadata rather than the actual data rows. It reflects the current index structure defined in the information_schema system tables. As a result, it executes almost instantly, regardless of the size of the table or the number of rows it contains. That’s why it’s the first thing you’ll want to check when sizing up a table’s indexing.
It’s important to note that this command does not reveal how often a key is used, whether it is effective for a particular query, or how selective it is in practice. It simply reports what indexes exist and how they are defined.

Still, SHOW INDEX does offer some key information for tasks like:

• Verifying whether a search structure already exists before creating a new one.
• Checking for duplicate or redundant paths.
• Reviewing the structure of composite keys.
• Confirming whether primary keys and foreign key constraints are present.

When you run a SHOW INDEX query, MySQL returns a result set with several columns that describe each search key’s structure and attributes:

Most issues stem from poor cardinality, ineffective composite indexes, or redundant lookup structures. Cardinality gives a rough idea of how selective the index is – higher values generally lead to better performance for filtering queries. However, because the value is estimated, it’s not always reliable, especially on tables with frequent changes.

For composite indexes, Seq_in_index shows the order of columns. This order directly affects whether a search structure can be used by a query. MySQL applies the leftmost prefix rule, meaning queries must filter on the first column, or the first few columns in sequence, for the index to be effective. Many developers mistakenly assume that any indexed column can be used independently, but this isn’t the case.

Lastly, Key_name and Non_unique provide a quick way to identify whether an index enforces uniqueness or serves as a regular secondary index. Recognizing duplicate or overlapping lookups at this stage can prevent performance problems later.

When reviewing indexes, the goal is not only to see which ones exist but to understand whether they are supporting queries effectively or adding unnecessary overhead. Let’s walk through three practical scenarios using a fictional sales database.

Suppose we inspect the customers table with SHOW INDEX FROM customers;. For clarity, we have simplified the output:

At first glance, having both email_idx and email_unique might seem harmless. But there’s a problem:

• email_unique enforces uniqueness and also serves as a lookup index for queries filtering by email.
• email_idx is a redundant non-unique key on the same column.

Maintaining both indexes adds overhead for every INSERT and UPDATE operation involving the email field. Since the unique key already supports both data integrity and efficient lookups, the non-unique email_idx serves no purpose.

The best plan of action is to drop the redundant index:

In MySQL 8.0+, you can also mark it as INVISIBLE first to test the effect without fully removing it:

Now, let’s examine the orders table with SHOW INDEX FROM orders;:

Now imagine that most queries filter by customer:

Here, the idx_customer_date lookup structure works perfectly. It allows the database to quickly locate all orders for that customer. But suppose we also have this query:

Even though order_date is part of the composite index, this query cannot efficiently use idx_customer_date because it doesn’t filter by the first column (customer_id). MySQL’s leftmost prefix rule prevents it from using the second column of a composite key alone.

What does this mean? When creating composite indexes, consider how frequently each column appears in WHERE clauses, and order the indexed columns accordingly. If queries frequently filter by order_date independently, it may warrant its own index.

Let’s say a developer wants to speed up the following query on the products table:

Before creating a new index, review existing ones:

There’s no search structure on sku. But before adding one, we ask two questions:

• Is sku used frequently enough in queries to justify an index?
• Is sku high-cardinality (many distinct values), making indexing beneficial?

Assuming the answer to both is yes, then we go ahead:

While SHOW INDEX works well for individual tables, it’s impractical for bulk reviews. Instead, the information_schema.STATISTICS table provides a centralized view of all index metadata across every table in a given schema:

This approach provides a broader view, allowing you to:

• Identify duplicate search structures created across multiple tables (a common issue in legacy schemas).

• Spot inconsistent naming conventions, which can complicate management and schema migrations.

• Verify foreign key indexes. Many performance problems trace back to missing lookups on foreign key columns, especially in tables handling high volumes of joins or deletes.

• Audit composite indexes. You can review whether their column order aligns with how queries filter and sort data.

For small to medium-sized databases, manually reviewing the metadata output may be sufficient. However, in larger environments or when performing routine audits, many teams develop scripts to export and analyze index metadata regularly.

But the best approach for advanced or enterprise setups is automation. Automated tools like Releem can continuously track search key usage and highlight potential improvements. Instead of reviewing structures alone, Releem tracks real-world query performance over time. It identifies slow or resource-heavy queries and highlights which search keys are (or aren’t) supporting them.

Releem ranks the top queries by execution time and overall impact on your server, helping you focus on the big offenders, rather than sifting through metadata or slow query logs manually. It also suggests potential improvements, including missed keys or query rewrites, duplicate indexes, and tracks these recommendations over time.

If you want more detail, see Releem’s full Query Analytics & Optimization overview.

Search structures tend to stay out of sight and out of mind. That’s both their strength and their weakness. When they’re working well, there’s no need to think about them. But as tables grow and query patterns shift, designs that once made sense can become a liability.

The hard part isn’t just adding new keys when things slow down. It’s knowing when existing ones have outlived their usefulness – and how to spot gaps before they cause real problems. You need to keep pace with how your data and applications change over time.

If staying ahead of those changes sounds like a lot to manage, Releem can help by tracking query performance and highlighting where improvements will have the most impact.