MySQL Index Limits: Maximum Number of Indexes, Columns per Index, and Design Guidelines

Preface

Hello, I am "Snail Boy". In daily development, backend developers often write SQL and consider adding indexes to query columns. This article answers common interview questions about MySQL index limits, including the maximum number of indexes per table, the maximum columns per composite index, and the problems caused by excessive indexes.

1. InnoDB Storage Engine

According to the MySQL official documentation, InnoDB allows up to 64 secondary (non‑primary) indexes per table. Adding a primary key index makes the total maximum 65 indexes.

Each index can contain at most 16 columns, as documented by MySQL.

https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html

Therefore, an InnoDB table can have a maximum of 65 indexes, and each index can include up to 16 columns.

2. MyISAM Storage Engine

For MyISAM, a table can have up to 64 indexes, not counting the primary key index. The column limit per index is also 16.

https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

Some developers confuse the per‑index column limit with the total index count; the former is 16 columns per composite index, while the latter is the total number of indexes allowed.

3. How Many Indexes Are Reasonable for a Table?

An index is a data structure that speeds up query retrieval, similar to a dictionary's table of contents. While indexes improve read performance, too many can degrade write performance.

Guidelines:

Small tables may not need any indexes.

Tables with frequent updates should avoid excessive indexing.

In most cases, adding indexes is necessary. Alibaba's "Java Development Manual" suggests keeping the number of indexes on a single table to 5 or fewer . This is an experience‑based recommendation rather than a hard limit; some tables may have 6‑7 indexes, but exceeding 8 often indicates a design issue.

4. Problems Caused by Excessive Index Design

Although InnoDB permits up to 65 indexes, it is generally recommended to stay below 5. Excessive indexes lead to:

Write performance degradation : Every INSERT/UPDATE/DELETE must maintain all related indexes, causing higher latency and lock contention.

Space consumption growth : Each index stores a copy of the indexed data; dozens of indexes on a million‑row table can consume several gigabytes of additional disk space.

Query optimizer overload : The optimizer must evaluate many possible index paths, increasing planning time and sometimes choosing sub‑optimal indexes.

Maintenance cost increase : Backup, migration, and schema changes (e.g., ALTER TABLE) become slower because all indexes must be rebuilt.

Each of these issues can significantly affect the overall health and scalability of a database system.