Avinash Dubey
Avinash Dubey

Reputation: 53

How many maximum possible indexes we can create in mysql table?

Is there any Maximum limit in MySQL table, that we can only create a fix amount of indexes in a table?

What is the Maximum possible number of index be created in a table?

Upvotes: 3

Views: 10824

Answers (2)

tfont
tfont

Reputation: 11253

@Jonast92 answer nails it.
Length per index is also important (more so for unique indexes).

I would also consider referencing this question's answer too:
https://stackoverflow.com/a/16568369/1804013

However, a better question perhaps would be; what's the maximum amount of suggested indexes to have?

As too many indexes are usually an indication that there's something wrong about the table design. Also, it's worth considering that too many indexes will slow down data manipulation.

An idea for index discovery:

  • WHERE clauses (including JOIN conditions on equal columns)
  • GROUP BY
  • ORDER BY

Upvotes: -1

Jonast92
Jonast92

Reputation: 4967

The maximum number of indexes per table and the maximum index length is defined per storage engine. See Chapter 15, Alternative Storage Engines. All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes. Most storage engines have higher limits.

From the MySQL Documentation.

Upvotes: 5

Related Questions